Veiwing formulas in Excel

[jt murphy]

Hi,

I want to convert my company from Lotus 1-2-3 to using Excel. The problem is that they are very fond of having a column, just to the right of a column of computed formulas, that shows the formula of the cell to it's left.

Lotus let's you format (ie Fixed, Comma, US Dollar, Formula) the cells as "formula" and it shows you the formula in that cell instead of computing it. So we are constantly copying the left column over to the right by cutting & pasting - BUT with one big exception. We use the "Paste Special" and unchecked the format check box. This leaves the format of the cell you are copying to unchanged.

Maybe if I tell you why they do this someone could suggest something else if it is not something Excel can do. The spreadsheets are printed out and checked and re-checked by a series of managers. Having the formula next to the cell allows anyone checking the spreadsheet to quickly see what the cell's contents are. This way they can sort of "debug" an error if they come across one.

 

[venkat1926]

suppose the formulas are in column C from C1 down
try this macro
you will see the formulas in column D
modify to suit you

Sub formulas()
Dim rng, c As Range
Set rng = Range(Range("c1"), Range("c1").End(xlDown))
For Each c In rng
c.Offset(0, 1) = "'" & c.Formula
Next
End Sub

 

[RalphA]

venkat:

Your code worked perfectly for me! Thamks.

Previously, I have been using the painstakingly slow method of highlighting one cell with a formula, adding a single quote, ', in front of the equals sign, then doing a Copy, going to the next cell, and doing a Paste, then going back to the original cell to remove the single quote. Then, on to the next cell... Takes quite a while! With your Sub formulas (), it's a cinch.

Now, how would one modify it so that it is universal, so that, say, if I go to cell R6, and run the sub formulas, it will copy the formulas in the range Q6 on down, to the range R6 on down.

 

[venkat1926]

Sub formulas()
Dim rng, c As Range
Set rng = Range(Range("R1"), Range("R1").End(xlDown))
For Each c In rng
c.Offset(0, -1) = "'" & c.Formula
Next
End Sub

I have given modifed sub
you have to do two things
set rng to be changed to R1 etc
as col Q is one column behind you use
c.offset(0,-1)
that is same row one colum behind.

still moer general it gets little more complicated but can bedone
you can select R1 and down and then istead of rng you can use selection

any other problem revert to newsgrup
venkat

 

[jt murphy]

venkat,

Thanks. I had to learn how to use macros but I got it to work. Pretty cool.

I tried to do it myself but could not. We usually have three to four of these senarios in all our worksheets. So I would need a macro that took the column "c" and put the fomulas in "d", column "e" to "f", etc? Is that hard?

Also, when you make a macro. Can you see it in any worksheet or do you have to re-create everytime? In that same thought, can you have it so other users can use them?

 

[venkat1926]

no. it is not difficult to create macros like that

read on "offset" in vba editor help.

in vba if you write
range("a1").offset (1,2) means one row below A1 and 2 column right of A1 i.e. C2. 0 means same row or same column depending where it comes.

you normally create macro in vbeditor (ALT+F11 you get vbeditor main window. click control+R to get all the opemed file. highlight your relevant file and in the menu click insert , moduole. in hte module window you type the macro. and save the file. whnever you open the file the macro will also be there..

venkat

 

[Wolfshead]

Hi
You can display all formulas in a sheet by pressing Ctrl+` (the key immediately below Esc. This toggles the worksheet display between formulas and returned values, and might mean that you don't have to add extra cells to show a formula. The alternative Menu to do this is: Tools, Options, View Formulas
Wolfshead