showing and copying a formula in a cell

[AmyTaylor]

Hi everyone, I have a spreadsheet which includes a number of complex
formulae in different cells to calculate values.
I have created a new sheet which includes a description of what the
cell does, but would like to include a sample of the formula.
Is there anyway to get the formulae in a cell instead of the value, and
then be able to copy that formula across as text into another sheet?

Thanks for your help.
Love
Amy xx

 

[Pete_UK]

You can put an apostrophe in front of the = sign of the formula to turn
it into text. Then copy this to your other sheet, and go back to the
original cell and remove the apostrophe.

Hope this helps.

Pete

 

[Dav]

I would make a copy of the sheet with the formula in it, the goto
tools_options_view and slect the formulas tick box. This will show the
formulas and then document alongside the formulas

Not elegant but a start!

Depending on the formulas you could replace = with nothing this would
turn the formulas into text althought and subsequent uses of the =
would also be lost

Or insert a ' at the start of the formulas you wish to retain

I am sure there is a better solution

Dav

 

[dmexcel]

Hi Amy,
put a " in front of the = sign then it will show up in the cell
because it won't be a formula anymore

 

[Pete_UK]

If you have a lot of formulae to copy over, you can highlight the area
in the first worksheet then do Find & Replace (CTRL-H):

Find What?: =
Replace with: z=

then replace all. This will convert all your formulae to text and you
can copy then to the other sheet as required. In the first sheet you
can then do this in reverse to re-instate the formulae, i.e.:

Find What?: z=
Replace with: =

Although this is quicker to convert to text, a big drawback is that if
you have a formula with = in it, such as:

=IF(A1>=B1, A1,B1)

then both = signs will get converted to z=, so you then have to amend
these on the second sheet.

Hope this helps.

Pete