Show formula but also its result shown in other column 1

[COII]

Dear all,

I have a question for using Excel. I would like to show formula in A1,e.g. 1+2+3 but its result shown is B1, e.g. 6.

Anyone can help me to solve it?

Thanks.

 

[Fenrirshowl]

Quick way is to write your own function in VB. Something like:

Function getform(cellref)
getform = cellref.Formula
End Function

You would use this function in A1, using B1 as the cell reference.

HTH

 

[COII]

Thank you. But it seems that it is complicated for me.

 

[GlennUK]

Hi COII,

can you tell us all which way round you want this to work ... do you want the formula that is used in cell B1 to be shown in A1, OR, do you want the formula that is in cell A1 to be used for the calculation in cell B1?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TommyP]

Write your formula and then copy it to the next column. Then highlight the cell/column that you want to show the formula in and do a find/replace to change the = to '=. This way the formula will display as text.

You may have to anchor your formula so the cell reference does not shift. =$A$1+$B$1.

Hope this helps.

 

[AnotherHiggins]

I would like to show formula in A1,e.g. 1+2+3 but its result shown is B1, e.g. 6.

A formula only affects the cell in which it resides. A formula cannot change any other cell.

In A1, type in:
[tab]'=1+2+3
(The apostrophe will tell Excel to ignore the = and just display the formula as a "string")

In B1, type in:
[tab]=1+2+3
The result will be displayed in B1.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

This VBAX KB entry comes complete with code, instructions and an example file ..

http://vbaexpress.com/kb/getarticle.php?kb_id=62

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SeekingAnswers2003]

Hi COII,

Don't know if you've done what was suggested above. Also, don't know why you need to display such information, but a very simple way in order to show multiple equations with its result, you can just type in cell A1 =1+2+3 and let it be a calculation. On the next column just enter =A1. Then if you just copy and paste special values for all of column B, you'll have a 6 entered instead of the formula. When you click both <ctrl>+<~> it will show any formulas entered into any cell.

Hope it is what you are looking for.

 

[COII]

Dear all,

Thank you for your solution. I just tried to all of your suggestions. That's great! Thank you very much.

Actually, I used it for calcuation of dimension (M3).

ie. carton size is 60x30x30cm, and I must calculate its cubic metre... But carton size must also shown worksheet.

 

[firefytr]

If you want to use the formula (=60*30*30) and the UDF in the link I posted, you can then use (assuming your formula is in A1)..

=FTEXT(A1)

.. and it will show ..

=60*30*30

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[firefytr]

Hit the post button before I was done.. sorry..

You could also set up some cells which are the Input cells for your three different dimensions. Lets say in A1 you have 'Length', B1 you have 'Height', C1 you have 'Depth'. Then in A2 you can put 60, B2 you can put 30 and C2 you can put 30. This will then make your formula..

=A2*B2*C2

This will also allow you to see all three parameters, Input and Output. You could use a concatenate to show your formula as well then..

="="&A2&"*"&B2&"*"&C2

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[GlennUK]

Hi COII,

if you want to enter:
60*30*30
in a cell, and have it evaluated in another cell. First, select the cell where you want the result to appear, and then create a defined name, ( menu command Insert/Name/Define ), for example called EvaluateCellToLeft, with a formula of:
=EVALUATE(A5)
( replace A5 with the reference one cell to the left of the current cell ).
Press the Add button, and the Close button.

Then in the cell for the result, type =EvaluateCellToLeft



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.