IF Function With No Circular Reference???

G

GBL

Hi:
I'm in need of help expressing a portion of an IF formula without using
a circular reference.
Three columns (B, E, and F) are referenced beginning at row 17. The B and
E columns are formatted as dates while F is formatted as numbers.
If F17 is a zero, then I need E17 to be the same date as B17. If that is
not the case, I want the date in E17 to not change.
Referencing the E17 in the E column creates a circular reference that I
can't seem to avoid.
IF(F17=0,B17,????????????). Any ideas???
 
S

Squier

GBL,

What is the location of this formula? It sounds like you
have the formula in cell E17 and of course that would
cause the circular reference.

=IF(F17=0,B17,E17)

This formula should work as long as it's not in cells
B17,E17, or F17.
 
A

Alex

How is the date in E17 generated

If it has a formula aready in it, use IF(F17=0,B17, other formula

Alex
 
G

GBL

Hi Alex:
The cell in E17 is usually erroneously entered by the user, which I'm
trying to correct. It's date formatted but, I can't seem to avoid a
circular reference. Can you help?
 
A

Alex

GBL

You can't have a cell that contains a formula and then enter data into it, it would erase the formula

Try entering the formula in col G and have the user enter their data in E

Alex
 
I

Ilan Rencus

I would enter the date in a new column, say H, instead of column E.
Than using an if formula like =IF(F17=0,H17,F17)in E17.
Ilan
 
G

GBL

Hi Alex:
You are absolutely correct!! Must have had a "Senior Moment"!! Thanks
for ringing my chimes!!
Those circular references leave a bad taste!!
Decided to use a conditional formatting to flag entry errors that do not
meet the disciplines.

.. Thanks for your help!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top