Custom Cell Format With Calculation

[johncassell]

Hi all,

I was wondering if it would be possible to have a calculation inside a cell format.

For example if I type 7 into a cell and the custom format of the cell is #"XXX" then the cell would display as 7XXX

But lets say I have 2 cells. Cell A1 is selling price per unit (£1.50), Cell A2 is Quantity (100). I would like some format that would take the Quantity figure that I enter and then put Quantity * Selling Price in brackets after it.

So if I enter 100 in cell A2 (QTY) then it would display as 100 (£150.00)

Pretty sure the answer will be 'it can't be done' but fingers crossed anyway!

Thanks
John

[Richard Buttrey]

Hi,

A format can't add anything to a cell it can only display it in a different way.
You can obviously do it with a third cell

=A2&" (£"&TEXT(A1*A2,"0.00")&")"

[johncassell]

OK, thought so. Third cell option it is then!

Thanks for the reply Richard, appreciate it.

John

[Richard Buttrey]

Second thoughts.

You could do it with a VBA Sheet Change macro

Please Login or Register  to view this content.