can i display the formula in just one cell?

[bigdan]

im dealing w/ a looong formula. i've tried evaluating the formula, which has helped, but i still need to go thru it myself. if i click show formulas ALLL the formulas on the page appear, and this page is pure formulas, so everything gets unreadable. is there some way to show the formula for just this one cell?

btw one thing ive done to make reading this formula is to name the cells being used as opposed to their references (what's this called? cell reference?). but i believe in some cases these names you assign are at the sheet level, some are at workbook leve. how do you toggle?

 

[bigdan]

edit - further to above, lets say one of my cells refers to the relevant part of the range of another column. eg A1 references data in C1, A2 in C2 etc. can i name C1:C5 "Data" and yet refer to it in the formulas in col A?

meaning, in A1 I'd like to replace every mention of C1 with "Data", same in C2 etc, but I'd still want A1 to refer to C1 (not the whole C1:C5 range). would Excel know where to look?

i hope this was coherent. dont blame you if not clear, if you're willing to help but not quite sure please let me know which part is confusing.

 

[hippiehacker]

can be done with UDF

Function GetFormula(Cell as Range) as String
   GetFormula = Cell.Formula
End Function

use it like this: =getformula(Cell)

 

[bigdan]

can be done with UDF

Function GetFormula(Cell as Range) as String
   GetFormula = Cell.Formula
End Function

use it like this: =getformula(Cell)

thanks for this, not sure what you're doing there though man if i was to type that formula into the cell i'm trying to see the formula of, wouldn't that erase the formula? ie if the loooong text is in A1, and i typed your code in A1 instead, the original value would be lost.

are you suggesting putting this into another cell? actually that didnt occur to me. but if so couldnt i just copy the original formula into another cell and precede it with an apostrophe?

 

[hippiehacker]

UDF means UserDefinedFuntion the code has to go into a VBA module of your workbook

Usage: Examples using GetFormula
=GetFormula(A1) --> Display the formula used in cell A1
=personal.xls!getformula(A1) --> invoke macro from another workbook
=GetFormula(sheet150!A1) --> get the formula used on another worksheet
=GetFormula('sheet one'!A1) --> other sheetname has spaces
=GetFormula([WBName.xls]WSName!A1) --> from another workbook with caution

 

[bigdan]

hahaha that was quite complicated (at my level that is) but i see where you're coming from. thanks for the help bud.

 

[GlennUK]

Do you know that you can change the height of your formula bar? Make it high enough to show all of your formula.

 

[bigdan]

Do you know that you can change the height of your formula bar? Make it high enough to show all of your formula.

oh yeah!! duh. forgot about that one. thanks dude.

 

[GlennUK]

oh yeah!! duh. forgot about that one. thanks dude.

My pleasure

( p.s. the shortcut to toggle the formula bar between normal and large-height is Ctrl-Shift-U )