MrExcel Publishing
Your One Stop for Excel Tips & Solutions

retaining a value

Posted by Ken Ledford on January 09, 2002 10:11 AM

I'm new to excel and hope this doesn't sound crazy. I need to retain the value of an if statement after the statement goes false.small example.Set the cell h1 to 1
the formula in my cell might read =if(h1=1,255).In my formula cell I'll get my 255.I need to retain the value when h1 next might be 2 and will have another value assigned to it.I just need a way to retain these values dumped off to another cell where they become hard values.I'm not even sure this is possible but thought i'd ask.
Thanks for any help

Posted by Jacob on January 09, 2002 11:25 AM


You can do this with vb

lets say the value you want is in A1 and we will save them in col z

sub sheet_change()
if target.column = 1 and target.row = 1 and target.value<>FALSE
range("Z65536").end(xlup).offset(1,0).formulaR1C1 = target.value
end if

end sub



Posted by Scott on January 09, 2002 11:25 AM

Not sure I follow completely. You want to retain the value from where? If the value is from H1, then you could just do =if(H1=1,255,H1). If you want it to give a different value for a different number, you could add the "or" function, or you could create a table and use a lookup formula. If this doesn't help, give me some more details, examples, of what you're trying to do.

Posted by Ken on January 09, 2002 2:16 PM

Thanks for a quick reply Scott. Again setting the h1 column = to the entered value 1(hard entry) in another cell I have a formula like =if(h1=1,255) which will give me in the formula cell the value 255. I need someway to be able to take this value and put it in another cell so that the value of 255 becomes a hard type entry that will not change depending on what happens in the formula cell. ie h1 will now become anothe entry like 2 and the formula cell will now be equal to another value. Of course the if statement will be constructed to detect what the value i wish to store is. formula cell would be similar to =if(h1=1,255,h1=2,260). I just need to be able to get the figures swlected by the h1 cell so as the don't interact as h1 changes.I know these values will need to be in a cell other that the formula cell. Hope this is a little clearer.

Posted by ken on January 09, 2002 2:22 PM

It looks like that might work but i've to do a little reading on implementing vb scripts.I can tell by you reply that it looks like it would work. Thanks for your help.