formatted values from single equation

[mr_nice!]

I would really like to have a Excel spreadsheet with two worksheets, the
first sheet has raw data (16 across, 100's down) and what I would like
to do on the second sheet is add to the first row, 16 equations (lets
say for now y=x). then underneath this the formatted data where x is
cell A1 on the first worksheet and y is on the second worksheet.

Obviously you would just normally in each cell on worksheet 2 type the
equation in (='worksheet1'!A1), but I would like to show people the
equation being used. Is this possible, does this message make any
sense??

Nice

 

[Bernard Liengme]

Not sure if I understand correctly but here is suggestion.
1) Window: New Window
2) Window : arrange Tile
3) In second window use CTRL+` (key next to 1 on top row of 'normal' keys)
In one windows we see data and results; in the other we see formulas
best wishes

 

[Gord Dibben]

Not sure what you need here but perhaps a UDF that will allow you to show the
formula in a cell adjacent to the cell with the formula and value.

Function ShowFormula(cell)
ShowFormula = "No Formula"
If cell.HasFormula Then ShowFormula = cell.Formula
End Function

Assume you have a formula in A1

In B1 enter =ShowFormula(A1)


Gord Dibben MS Excel MVP

 

[Bernard Liengme]

Slight improvement

Function ShowFormula(cell)
ShowFormula = "No Formula"
If cell.HasFormula Then
ShowFormula = cell.Formula
Else
ShowFormula = cell.Value
End If
End Function

 

[Gord Dibben]

Bernard

Your function shows whatever is in the cell or 0 if nothing, while my version
will show the formula or if none, the message "no Formula"

I don't know what the improvement would be.


Gord

Slight improvement

Function ShowFormula(cell)
ShowFormula = "No Formula"
If cell.HasFormula Then
ShowFormula = cell.Formula
Else
ShowFormula = cell.Value
End If
End Function

Gord Dibben MS Excel MVP

 

[mr_nice!]

Thaks guys. Never wrote a function in Excel. Where can I get more inf
on it and what is a UDF?

nic

+-------------------------------------------------------------------
|Filename: example1.jpg
|Download: http://www.excelforum.com/attachment.php?postid=4894
+-------------------------------------------------------------------

 

[Beege]

mr_nice,

Maybe start with this

http://www.meadinkent.co.uk/xlformtext.htm

Beege