If formula and circular reference

**jcavigli** · 10-11-2008, 09:44 PM

I am trying to design a template for work. I am having trouble writing an IF formula because it contains a circular reference. Please see the attached sample.

This is what I would like to happen. The user will enter text starting with the eVestment worksheet for each product. If any text value in eVestment B8:B16 reads 'Not Available' then I would like the corresponding value on the other worksheets to say 'Not Available.' So if eVestmentB8 reads 'Not Available' I would like:

BrockhosueB8 to say 'Not Available'
CambrigeB8 to say 'Not Available'
CallanB8 to say 'Not Available'

If eVestmentB8 does NOT read 'Not Available' I would like:
BrockhouseB8 to say any value the user writes in here
CambridgeB8 to say any value the user writes in here
CallanB8 to say any value the user writes in here

I tried entering a formula and no luck. Could someone please view my work and let me know a way around this?

**NBVC** · 10-11-2008, 09:50 PM

If eVestmentB8 does NOT read 'Not Available' I would like:
BrockhouseB8 to say any value the user writes in here
CambridgeB8 to say any value the user writes in here
CallanB8 to say any value the user writes in here

You cannot have a formula in the same cell that you might enter hard data into. You may need to add another column next to the one where user enters data that will choose whether to say "Not available" or the value the user entered.