Solved DEBUGGING: Nested drop down in an IF stmt using data validat

March 13, 2013 at 12:22:59
Specs: Windows 7

I'm trying to get the following formula to function.. and as it's setup the final [false] parameter keeps showing up, as IF it's ignoring the rest of the IF statement.

The formula is to pull a title based on an inputted rate, where rates 6 and 7 have multiple titles. I would like the specified title presented when there is only one choice and a drop down for title selection when there are two choices for a single rate.

For reference:
A32:A41 are titles and B32:B41 are rates.

Cell A66 contains the test "No Valid Rate Selection". When I use data validation to handle the formula, I am ONLY returned whatever is the final FALSE, in this case the contents of A66, not any other of the possible titles.

=IF(B7="RATE1",A33,IF(B7="RATE2",A34,IF(B7="RATE3",A35,IF(B7=”RATE4”,A36, IF(B7=”RATE5”,A41, IF(B7="RATE6",INDIRECT(Rate_150),IF(B7="RATE7",INDIRECT(Rate_135),A66)))))))

See More: DEBUGGING: Nested drop down in an IF stmt using data validat

Report •

March 13, 2013 at 13:39:20
✔ Best Answer
Take a look at the difference between the quotation marks around RATE1, 2, 3 as compared to RATE4 and RATE5. RATE4 and RATE5 are not enclosed with the type of quotation marks that Excel expects.


When I pasted your formula into a spreadsheet, I could not get it to work correctly until I changed them to "regular" quotation marks. In addition, I did not set up any dependent drop downs, I simply named a couple of cells Rate_135 and Rate_150 and put a cell reference in each.

Once that was set up, your formula worked fine.

Maybe your problem is related to the actual contents of the cells or the drop downs because other then the quotes, it worked fine for me.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question