How do I get just 1 cell to display it's formulae not whole sheet?

M

MrsWiz

Want to be able to see and print just one, or a few, cells showing their
formulae; not have to have the whole sheet displaying their formulae.
 
M

MrsWiz

Barb thanks.
This looks like Macro add-ins the FORMULATEXT function resulting in the
display of the formulae in a cell within another cell that is possibly off
the sheet or the like.

I'm after being able, instead of the Tools\Option\View_Formulas function
which shows a whole sheet's formulae, to change a cell or two to show their
formulae whilst leaving the rest of the cells in the same sheet that contain
formulae displaying resulting values.
I'm thinking that Conditional Formatting using the Formula IF statement
telling the cell what to display if it contains 'such and such a formula'
then 'display this formula' is perhaps the way to go? This would mean
formatting each applicable cell with the IF statement display and it could
not be applied by highlighting the first cell and dragging/ copying to
others.
Suggestions please.
 
P

Pete_UK

Conditional Formatting can affect the colour and font effects in a
cell, but it can't do what you describe. I think you would need some
VBA to do that - perhaps, you change the background colour of the cells
you want it to apply to (eg to yellow) then invoke the macro. This
would look for yellow-coloured cells and display the formula in there
if there is one. You'll also need to undo the effect, so perhaps it
would be better if it toggled the display each time you ran it.

Hope this helps.

Pete
 
J

jkend69315

MsWiz, This little macro may do what you want. Open the workbook and
display the worksheet. Press Alt-F11 to go to the code editor. From
the menubar, select Insert, then Module, and paste this code in there.
Alt-F11 to return to the worksheet view. When you want to use it,
select the cells you want to show formulas, click Tools, then Macro,
then Macros. When the Macros form appears, the sub should be
highlighted. Click Run to run it or click Options if you want to
assign a short-cut key to it. It will toggle the selection back and
forth from values to formulas. HOWEVER, it places a space in front of
the formulas in order to show them, so they will not work while
displayed as formulas, a problem if other cells use these formulas in
their formulas. Hope it's of some help to you.
James

Sub ShowFormulas()
Dim cell As Range
For Each cell In Selection
If Left(cell.Formula, 1) = " " Then
cell.Formula = Right(cell.Formula, Len(cell.Formula) - 1)
Else
cell.Formula = " " & cell.Formula
End If
Next cell
End Sub
 
Top