Want formula to turn to value by itself

[DougL]

Is there any way to have a formula within a cell automactially change to a
value by itself? To put this into context, here's a small example. In cells
A1:A12 I have the title of the months plus "forecast" (Jan Forecast, Feb
Forecast, March Forecast,...Dec Forecast). In cells B2:B13 I have the exact
same lookup formula referencing the same cell, G1 (value of 10). I use the IF
formula to not show anything in cells B2:B12 (for now), so let's just
concentrate on cell B1. So my result in B1 = 10. Cell G1 will change
automatically in another month to another value of say 23, so now I want cell
B2 (Feb) to show the 23, but cell B1 to still show 10. In order to do this
cell B1 has to convert to a value instead of a formula. Is there something I
can do rather than manually copying and past valueing the cell?

 

[Earl Kiosterud]

Doug,

Only a macro can change the cell to something else. But this might be doable with a
self-referencing formula (circular reference). They are a bit of a pain, though. Why
don't you give your cell formulas and values? That'll be a bit less vague than your
description.

 

[Ollie4]

You can use a combination of IF and INDIRECT, like

In A1 I have a validated cell to choose between BU1 and BU2
In A2 i have an If statement:
=IF(A1="BU1";"SUM(B1:B10)";"Count(C1:C10)")
In A3 I have =INDIRECT(A2)

Now if I type BU1 in A1 this will return the formula in A3 to
=SUM(B1:B10) and BU2 will lead to =Count(B1:B10) in A3.
You can use any other condition as tricker or formula, nested IF's or
whatever....and without a macro