IF this then that else LIST

**alexis_tanner** · 12-20-2011, 04:59 PM

I would like to write a function that performs this:

=IF(F8="Calgary","No",IF(F8="Vancouver","Yes",???????))

Where ??????? returns a LIST (currently named "LOAs") where the user can pick Yes, No or Partial.

I am not sure how to reference the list in...

If I reference the cells that have the list components I get a #VALUE error.
If I reference another cell which has the data validation, I get a "0".
If I reference INDIRECT(LOAs) I get a #REF error.

Please help! I can't figure it out from the other forums I have seen out there.

Thanks! Alexis

**Paul** · 12-20-2011, 05:05 PM

Hi Alexis, welcome to the forum.

You can't do this using a formula. Allowing a user to select a value after other conditional tests have failed would require a macro (either automatically via change event, or can be run manually).

**alexis_tanner** · 12-20-2011, 05:10 PM

Well that's comforting that it's not my fault it's not working...

Can you tell me more about how to do it with a macro?

**alexis_tanner** · 12-20-2011, 05:21 PM

Well I thought I'd tried this already but it worked very simply.

Are Letters of Assurance required?
=IF(F8="Calgary","No",IF(F8="Vancouver","Yes",""))
And then I applied the data LIST validation to the cell as normal.

It allows me to choose from the list (for any other city) when it doesn't choose automatically and accepts my choices when I need to.

Also the cell that references this cell (and repeats whether or not an inspection is required based on this first answer) understands the blank, the yes or the no. So I'm golden!!

Thanks for your help anyways!

**Paul** · 12-20-2011, 05:34 PM

Glad you figured out a possible solution, but just be aware that once the user selects a value from the drop-down list, the formula you had in the cell disappears and does not return.