showing values instead of formulas in the box above

[Legacy 23340]

hi i have a list of couple hundred cells and would like to do some modifications to their display mode.

meaning say for example i have a cell J10 i have this formula

=$B$1*$B$2+$B$3*85 and in the cell where the formula fits in is a numeric value 19127

Is there something i can do to hide the formula =$B$1*$B$2+$B$3*85 and show only 19127 when i move the cursor over the cell with the arrow keys? and show it in the box up above.

i would have to apply the modification to hundreds of cells.

looking for simple help here.

TIA.

 

[MartinK]

This could partly help you:

In Format => Cells, Lock, check "Hide Formulas"

Go to tools => Protection, and protect the sheet.

Martin

 

[Yogi Anand]

Hi water:

this is how I can do this in EXCEL 97 ...

1. select the cells of interest for which you want to hide the formula

2. invoke FORMAT|Cells|Protection ... and tick mark the CheckBoxes Locked and Hidden

3. Protect the Worksheet (optionally with a password)

that should do it.

Does it help?

 

[Legacy 23340]

thanks for the responses i tried that long before i posted here the protection format cells locked protected sequences

The sheet remains protected the formulas don't show but the fact is the 19127 figure does not show either and i want it to that is the main jist of the matter.

any other solutions?

TIA.

 

[MJM]

Water, I'm dealing with the same problem. I want to show only the formula value, but the protection trick hides everything.

I've encountered workbooks that hide formulas AND show values, but I don't know how it works.

Did you finally get this to work?

Thanks

Mike

 

[Legacy 23340]

no luck. was hoping the experts would have something to share.

GL.

 

[Tom Urtis]

Is there something i can do to hide the formula =$B$1*$B$2+$B$3*85 and show only 19127 when i move the cursor over the cell with the arrow keys? and show it in the box up above.

The only time you see the formula is if you look at it in the formula bar or if you access the cell via edit mode. The value being returned is what you see in the cell itself when you are looking at the cell.

i would have to apply the modification to hundreds of cells.

No you wouldn't. In fact you don't need to touch a single cell. Just right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim y As Range, z As String
With Target
z = .Value
For Each y In Cells.SpecialCells(1)
y.Comment.Visible = False
Next y
On Error Resume Next
.ClearComments
On Error GoTo 0
If .HasFormula = True Then
.AddComment
.Comment.Visible = True
.Comment.Text z
End If
End With
End Sub

You need to explain what you mean by "the box above". If "box" to you means "cell", then you'd run into problems when formulas are in row 1. If "box" to you means "comment", the above code places the returned value of the formula in the comment when you hover your mouse over the cell, or when you click on the cell or when you select the cell using the navigation arrows as you said.

If you don't want the formula to be visible in the formula bar, then just add the code to this SelectionChange event to hide the formula bar upon selecting a cell that contains a formula, or temporarily replace the formula with the z value variable and replace the formula in the code at the end of the procedure.

 

[Legacy 23340]

thanks for the response, by box above the mean the formula box or bar what it is called i think.

when you double click in a cell and start to type a formula in the cell you see the same info/code being typed out in to the formula box above.

 

[Tom Urtis]

when you double click in a cell and start to type a formula in the cell you see the same info/code being typed out in to the formula box above.

Yes, that's what the formula bar does. To see the returned value in the formula bar, click in it and press the F9 key. Hit the Esc key to exit. The formula bar shows formulas.

If you want the formula bar to otherwise show returned values beyond what I just described then you need to change the formulas in the cells to static values (at which point they are no longer formulas), but that would be bad for other cells dependent on that cell's formula.

 

[foilprint0]

Hi

I followed the instructions below and get a "Run_time error 1004 no cells found.
In the debug window the following message apears
For Each y In Cells.SpecialCells(1)

Just right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim y As Range, z As String
With Target
z = .Value
For Each y In Cells.SpecialCells(1)
y.Comment.Visible = False
Next y
On Error Resume Next
.ClearComments
On Error GoTo 0
If .HasFormula = True Then
.AddComment
.Comment.Visible = True
.Comment.Text z
End If
End With
End Sub