Veiwing formulas in Excel

jt murphy

New Member
Joined
Oct 16, 2006
Messages
2
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

Code:
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

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
Code:
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

New Member
Joined
Oct 16, 2006
Messages
2

ADVERTISEMENT

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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

Board Regular
Joined
Oct 18, 2006
Messages
100
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
 

Forum statistics

Threads
1,136,507
Messages
5,676,263
Members
419,616
Latest member
quickflip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top