Display formula instead of formula result in Excel 1

[nitin36537]

Is there any way in Excel to display cell formula in a selected range?

In lotus 123 there is option by which we can format cells in such a way so that only those cell display cell formula

In excel when we use tools->option & check formula check box all the cell display formula ( not a selected range like lotus) Is there any way similar to lotus??

This will help when we have to submit calculation to consultant who want to check our calculation.I does this using Lotus like this

A B
1 1+2 =A1
2 4+5*10 =A2

I format column with /range,format,text
so column dispaly formula as shown above while column B display result of column A (B1 DISPALY 3 , B2 DISPLAY 54)

Nitin Patel
India

 

[Mala]

Dear Nitin

Alas, Excel has no direct way in which u can display formulas in a selected range. However, you could try the following:

Let's say u wish to see the formulas in a range in 'Sheet1'.

Make use of the fact that the tools->options-> formulas checkbox is in 'window' section of the option multi-tab options dialog.

1.Make 'Sheet1' active in the current window

2.Open a new window and make 'Sheet1' active there too

3.Format window 2 to show formulas (as u've described)

4.Tile the windows horizontally or vertically as convenient.

5.Scroll around in both windows

The advantage is u see formulas in one AND the calculated values in the other simultaneously.

The down side is u have to scroll in two windows - but I think the benefit is worth the effort.

I'll see if I can do some more on this - for now, the above will have to suffice

Regards
Mala

 

[Mala]

Further to yesterday's response:

Some VBA code is required to achieve the objective:

1. In the workbook insert a new module and enter the following:
In the Declarations section (top) put:

Public FormulaWin As Window, OriginalWin As Window, FormulaMode As Boolean

Below that Copy the following code (containing 3 procedures):

Sub SetFormMode()
Set OriginalWin = ActiveWindow
ActiveWindow.NewWindow
Set FormulaWin = Windows(2)
Windows.Arrange ArrangeStyle:=xlTiled, ActiveWorkbook:=True, SyncHorizontal:=True, SyncVertical:=True
FormulaWin.DisplayFormulas = True
End Sub

Sub ResetFormMode()
FormulaWin.Close
Windows(1).WindowState = xlMaximized
End Sub

Sub WhatToSet()
If FormulaMode Then
ResetFormMode
Else
SetFormMode
End If
FormulaMode = Not (FormulaMode)
End Sub

2. Assign ctrl+f hotkey to the macro 'WhatToSet'. This macro is designed to work as a toggle to set the view mode off and on.

3. In the 'On' mode you'll see two windows in which both hor. and vert. scrolling are synchronized.

4. ctrl-f to turn this off.

Comments:
Though scrolling is synchronized, the column width in the values window will most likely be less than that in the formula window - synch. scrolling simply ensures that the top left cell in both windows is the same.
So its possible that u may select a cell in the 'values' window which is not visible in the 'formulas' window. To avoid this situation you may do your navigation in the 'formulas' window - this way u're guaranteed that the active cell is visible in both windows.

Or u may scroll around with scroll lock 'On' - but in this case the active cell may scroll out of the view zone.

All this is OK but I can't visualise the need to view formulas like this - as it is u can see the formula in the formula bar (I admit, for the active cell only) and the value in the cell.

Good luck!

 

[yakpol]

Nitin,
Mala is right, there's no direct way (like formatting) to make formulas visible. The following macro will do the job you want.


Sub DisplayFormulas()
Dim rng As Range
For Each rng In Sheets(1).Cells.SpecialCells(xlCellTypeFormulas, 1)
rng = "'" & rng.Formula
Next
End Sub

yakpol

 

[nitin36537]

Hi Mala

Can I send Lotus worksheet to you?
You will understand how I am taking advantage of this feather (displaying formula & result on the same sheet).

Nitin

 

[yakpol]

Here's the simpliest solution: write custom function in VBA and use it in the spreadsheet

Public Function F_LA(rng As Range) As String
F_LA = rng.FORMULA
End Function

 

[Mala]

Hi,

Nitin: u can send me the file at mala_rs_singh@rediffmail.com - but I abandoned 123 about 5 years ago and don't have it installed on my system - but i'll try to check it out elsewhere - pl. also tell me which version of 123 u're using.

Yakpol: The sub Display_Formulas is a very good idea. Only reversing the process might cause problems with some genuinely bonafide cells containing strings beginning with "=". Such cells are not unusual in engineering spreadsheets in which computational steps are shown.

Everyone: Hope u all don't find me tiringly verbose - its the only way I know how...Don't be intimidated by the size of what follows - a good portion of it is just copy/paste material.

I applied my mind some more to the problem and came up with something I think u'll find interesting ...

It involves the use of the 'Conditional Formatting' feature available in Excel 97 onwards and (of course) VBA.

The idea was that if the active cell were highlighted with special formatting in both windows (say italics, border around and grey fill) it would be stand out in the inactive window, thereby making it easy to locate.

Note that this would apply only to the ACTIVE cell - i.e. as soon as u select some other cell in any of the windows, the new cell becomes the active cell - the old cell then should appear normal and the italics, border around and grey fill conditional format would apply to this cell.

To achieve this we need to do the following:
(I repeat the code above in case there may have been some changes):

1. In the workbook insert a new module and enter (or copy/paste) the following code:


Public FormulaWin As Window, OriginalWin As Window
Public FormulaMode As Boolean, ActAddr As Range

Sub SetFormMode()
Set ActAddr = Range("ActiveCellAddr&quot
Set OriginalWin = ActiveWindow
ActiveWindow.NewWindow
Set FormulaWin = Windows(2)
Windows.Arrange ArrangeStyle:=xlTiled, ActiveWorkbook:=True, SyncHorizontal:=True, SyncVertical:=True
FormulaWin.DisplayFormulas = True
FormulaMode = True
End Sub

Sub ResetFormMode()
If Windows.Count = 2 Then
FormulaMode = True
FormulaWin.Close
Windows(1).WindowState = xlMaximized
End If
FormulaMode = False
End Sub

Sub WhatToSet()
If FormulaMode Then
ResetFormMode
ActAddr.ClearContents
Else
SetFormMode
End If
End Sub


2. In the event module for Sheet1 copy/paste:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If FormulaMode Then
ActAddr = ActiveCell.Address
End If
End Sub


3. Assign ctrl+f hotkey to the macro 'WhatToSet'. This macro is designed to work as a toggle to set the view mode off and on.

4. Name the cell 'IV1' ActiveCellAddr - it is presumed u have no data in this cell. In the unlikely event that u do, u can name any cell which u're sure will NEVER contain data with this name.

5.Select cell A1 and do Insert>Name>Define, in the name box type CFrm and in the Refers to: box type =(ADDRESS(ROW(!A1),COLUMN(!A1),1)=ActiveCellAddr)

6. Select the entire worksheet, or, better still, the range containing data and do Format>Conditional formatting... In Condition1 (of course this presumes u've not used conditional formatting on Sheet1 for any other reason) select formula is instead of cell value is int the dropdown combo on the left. In the ref-edit box to the right type =CFrm. Click Format... and set the type of formatting u want for the highlighted cell (I used italics, border around and grey fill).

7. Now just hit ctrl+f and navigate in any of the windows to your heart's content...also, ctrl+f to land!!

Of course everything from 4 to 6 above could be automated in VBA, the mechanism could be extended to include other sheets, the conditional formatting (consumes memory) could be removed or applied depending upon whether the toggle disabled or enabled the formula view mode - the amount of fine tuning u can do depends upon your wish and need and your imagination... you won't find Excel wanting.

Moral of the story: Most good things originate from the thought 'If only I could...' and the endeavour to make it real.

Good luck!!

 

[nitin36537]

Yakpol

Your idea is good but it is reversel of what is required (what I do in Lotus) . What is required is I feed some formula in cell & next cell display rrsult of formula, but at the same time in the first cell formula should display it self (not result)

I thik it will be possible in excel, if we have some function which evalute result of input string.

like

cell A1 contain '4+5*6 (text with out = sign)
& cell B1 contain formula =evalute(A1)
cell B1 display 34


Nitin Patel

 

[yakpol]

Nitin,
Now I understand exactly what you want!
Here's your function.

Public Function EvaluateFormula(rng As Range) As Double
Dim str As String
EvaluateFormula = Application.Evaluate("=" & rng.FORMULA)
End Function

Thanks for the idea I'll use it in my calc's!

Yakov

 

[nitin36537]

Hi Yakpol

Thank You very much.
Yes this is what I need.


I came accross a module which is a possible macro virus.
Do you need copy of the same in text file?
many times we are not been able to save excel file.

This module was in "thisworkbook" which I copied in to a text file.

Let me know.


Nitin

 

[swertel]

I've been following this thread and find it very helpful, but currently don't have any need for this. Can the user that found the BEST way to do this create a FAQ?

Thanks,
--Scott

 

Hi Guys
Check out MathLook for Excel at

http://www.uts.com

to display Formulas in 2d format and easy cell renaming!!!