Another EXCEL FORMULA DISPLAY QUESTION 2

[FM1]

This is a question in line with thread770-37797,"FORMULA DISPLAY IN EXCEL".

In that thread several functions were offered as to how to display a cell formula in another cell, eg "GetFormula".

Now, my question is, how can it be manuplated to display the cell values rather than the cell addresses??
i.e., instead of A1+B6....., display the value shown in the A1 and B6 cells.
I have spent some time trying with not much success, then again, I am not very handy with puting user functions together.

I would really appreciate any input that any one of you may have.

Much Thanks.

 

[pmover]

the cell value is alsways displayed, unless the option to display formulas is activated (from previous mentioned thread) or a function (some code) is written to display something else.
if display formulas is activated and you want the values displayed, like the function getFormula, simply create a function similar to getFormula, but replace the code with:
GetValue = Cell.Value.
i've not checked and tested this, but from memory, i believe it should work.
-pmover

 

[FM1]

pmover
thank you kindly, forgive me if i dont follow, but since the value of the original cell is already displayed i dont need to display it again. what i am after is, say, cell A5 formula is "=B1+C1" and the values in B1 and C1 are 2 and 5 respectively, I am after displaying 2+5 in B5 insteat of B1+C1 or 7 .

I will appreciate it if yourself or anyone else in the forum could help

 

[IRstuff]

There is no simple way to do this, unless you're only talking about a relatively simple and/or constant equation.

If that's the case, you can use VB's string search functions to search for the "+" signs or other operators that you know of apriori. This is necessary because the function for finding unknown operators would be quite involved. Ditto for parentheses, you'll need to know where and how many there are.

After finding the operator, you can strip out the cell or name reference and then concatenate a string using the content value reference and the operators.
TTFN

 

[IRstuff]

Please see the following thread, actually, it's only one posting, but it's a beaut:

thread770-41701

The TKSolver guys have an add-on for Excel that displays formulas as regular equations and has a selectable level of substitution.

TTFN

 

[yakpol]

FM1,
You will find your answer using VBA function disfor(), provided at:

http://www.geocities.com/SiliconValley/Hills/1829/VBA/disfor.htm

It displays the values in the formula instead of references.

 

[19652022]

This is a great site suggested by Yakpol.What about getting the source code?

 

[zdas04]

An intermediate answer bewteen =A1+B1 and TKSolver or VBA is to name your cells. Up in the left corner is a box that usually says B1, but you can go up and change it to "Press" and then every time you want to use it you don't have to say "$A$1" you can say "Press". I regularly do this so that I can say =Press*SG/Rair/Temp which I find to be really useful.

David

David Simpson, PE
MuleShoe Engineering

http://www.muleshoe-eng.com

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips Fora.

The harder I work, the luckier I seem

 

[iken]

Why not use =B1&" + "&"C1

You can omit the spaces surrounding the + if you wish