Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Latch a Value with No Circular Reference Error in Excel 2010

Avatar of Theodore Dinsmore
Theodore Dinsmore asked on
ProgrammingMicrosoft ApplicationsProgramming Theory
9 Comments1 Solution1754 ViewsLast Modified:
Excel 2010 problem:
My question is quite close to the "Excel Circular Reference" question.

                   Col D        Col E         Col F
Row 18       1 or 0      (value)        =IF(D18=1,E18,F18)

What I am trying to get Excel to do is latch in the value of E18 into F18 on command:
"If D18=1. make F18 a copy of the value of E18;
 when I set D18=0, leave F18 as-is."

Since there seems to be no way of telling Excel to do nothing if D18 is changed from 1 to 0,
I am telling Excel to copy the current contents of F18 to F18 (thus leaving it unchanged).
This does what I want, but results in a circular reference error.
How do I avoid the circular reference error?  In other words,

Is there any way to
1. put a value in E18
2. set D18 to 1 to get the value copied to F18
3. set D18 to 0 leaving the copied value in F18
4. change the value of E18 some time later
5. set D18 to 1 to latch in the new E18 value in F18
6. set D18 to 0 leaving the copied value in F18
etc. without getting a circular error message?