Displaying formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to display formulas in only 2 columns of the trial balance worksheet,
and the rest of the worksheet to display the values in the proper format. I
have tried displaying the values, but it changes the whole worksheet.

Any suggestions?

Thanks...Debbie
 
I am not sure what you mean, do you mean you want to display the formulas as
text string in 2 columns but in all other columns having them displayed
normally?

One possible way, select the 2 columns you want as text strings, press Ctrl
+ h, in the find box put

=

in the replace box put

^=^


select replace all, close the replace dialogue box

would look something like this

^=^SUM(B2:B7) 9
 
Cannot be done without a bit of VBA.

Function ShowFormula(cell)
Application.Volatile
ShowFormula = "No Formula"
If cell.HasFormula Then ShowFormula = cell.Formula
End Function

Usage is: =ShowFormula(cellref) as in =ShowFormula(A1)

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP
 
Hi there, thanks alot for your response, I tried what you've suggested, and
it works, but if I have something in a column after that, and now it doesn't
read the formula anymore. What I need to do is show columns E & G with what
the formula is but all the other columns, before and after columns E & G to
show the correct values in the correct format. Does this make more sense?
Thanks alot, Debbie
 
Have a look at my first response Debbie.

You will retain your original formulas but see them in the helper columns.

Question..................why do you want to see the formulas and not the
results?


Gord Dibben MS Excel MVP
 
I tried that at work today, and it worked. That's great. I have 2 other
questions......if there is no formula, just a value, is there a way to show
the value instead of saying "No Formula", also when there is nothing in the
cell, is there a way to leave the cell blank, instead of saying "No Formula"?

The reason behind this is for an accounting firm that is doing a trial
balance. So they would like to show the amounts of CR & DR adjustments
before the total columns. (they use to use Lotus123)

Thanks alot for your help!
 
Revised.

Function ShowFormula(cell)
Application.Volatile
ShowFormula = cell.Value
If cell.HasFormula Then ShowFormula = cell.Formula
End Function
This on its own will leave a zero if the cell is blank.

To overcome that that enter as follows same as for any other function.

=IF(showformula(a1)=0,"",showformula(A1)


Gord
 
Back
Top