Need to keep value of a cell constant after it is derived.

[mitmir]

I have a function for cell E2, which generates a value based off of information from two other cells, D2 and I8, and a list. Once entered the information in D2 will remain constant, but I8 will change as the sheet gets used and this will cause Excel to recalculate the value for E2. Is there a way to keep the value of E2 from changing once it is calculated? I have tried using differing IF statements but generally end up with circular errors. I also found some macros that would lock the cell once used, and while some would keep the user from changing the value, Excel could still change it.

Don't know if this will help, but here is a sample of the in E2.
=IF(D2="Unaligned",VLOOKUP(B2,All,6,FALSE), IF(AND(D2="Khorne",$I$8=1),VLOOKUP(B2,All,4,FALSE), IF(AND(D2="Khorne",$I$8=3),VLOOKUP(B2,All,5,FALSE), IF(AND(D2="Khorne",$I$8=4),VLOOKUP(B2,All,5,FALSE), IF(AND(D2="Khorne",$I$8=2),VLOOKUP(B2,All,6,FALSE), IF(AND(D2="Khorne",$I$8=0),VLOOKUP(B2,All,6,FALSE),

I have basic knowledge of Excel and none of VBA, so this may be sloppy.

[Jakobshavn]

Once the value in E2 is satisfactory, copy the cell and PasteSpecialValues back onto itself.

[ben_hensel]

So basically you have

E2 = FUNCTION(D2, I8, {lookup})

but you want it to be

E2 = FUNCTION(D2, old_value_of_I8, {lookup})

The problem is, you want excel to "remember" an old value of I8, which is... not easy.

Plus, you are going to be creating an extremely opaque function; if you have other users, they will open it up, and it will be extremely difficult for them to understand what the heck is happening. So in addition to being not easy, I think it is not wise.

My advice is,

E2 = FUNCTION(E8, J8, {lookup})
J8 = user_input_from_chosen_I8

Don't overthink this. Just change it to a reference to another cell, and make that cell a copy/paste(value) from I8, which you change manually, because it doesn't sound like there's a time when you want it to change automatically.

[mitmir]

Thank you for the quick responses. I was aware of the copy-paste ability, but guess I was trying to avoid it or possibly automate it.
I8 does update automatically, as a consequence of new information being added, but that doesn't negate the idea of having the user input a returned value that wouldn't change over time.

thank you for the help