Arrrgg... Excel's P****** Me Off

[Fujirich]

Right from the top, I'll admit I'm not the greatest Excel expert. But this one I can't understand. Excel allowed me to do this once on the sheet - now it won't allowed this same data validation formula a second time (or a third). Here's the lowdown:

I have a named range called: RGU_Type. It's a single column of data with about 5 rows.

I want to use the values in that range for a data validated list on another page. BUT - I only want the list options to be available if a specific condition exists in another cell.

So, using data validation, I selected the list function, and entered the following formula: =IF($O$80="BC-7",RGU_Type,FALSE)

I'd never tried an "IF" statement to control a data validated list before, but I thought "let's see if it works". And sure enough, it did! Great - - I thought...

But I have two other instances on the same page where I need to set up the same thing in two different cells. The lists in those cells will in turn be controlled by results O89 and O98.

Now, every time I try to set up the same thing in another cell, I get the message: "The list source must be a delimited list, or a reference to a single row or column."

I've tried copying from the one cell that works to the other location I need, and then only change the reference of 80 to 89 - but no joy; I get the aforementioned message.

Why is this working fine in one place on the sheet but not another? Any ideas are greatly appreciated!

Rich

 

[Jonmo1]

The lists in those cells will in turn be controlled by results O89 and O98.

Will the SAME Named Range be used? Or do you have seperate named ranges for each? If using seperate name ranges for each, are they also using SINGLE column Ranges?
Are you using same formula for the validation, just changing Cell Ref?
=IF($O$89="BC-7",RGU_Type,FALSE)
=IF($O$90="BC-7",RGU_Type,FALSE)

 

[Dan Waterloo]

Just a guess:

Have you included the initial equal sign in the formula on your later validation definitions?

 

[Fujirich]

Jonmo1 - you're correct in all your assumptions. It's the same range and with teh same cells used for the list in all three instances. Its just the first part (the cell where I'm looking to be equal to "BC-7") that changes.

 

[Fujirich]

Dan - good guess, becuase you'd think it could be a simple syntax error. But that's not it.

Here's a copy of one equation that isn't working: =IF($O$89="BC-7",RGU_Type,FALSE)

 

[Fujirich]

Ok - I think I've figured this out - but I still don't understand why it works this way.

I've found that the condition must be TRUE when entering the equation and setting up the validation. If it's true, Excel will accept it and let it work. If it's currently FALSE when trying to enter the validation, the previously mentioned message will pop up and Excel won't let you proceed further.

I've got it working, but still can't understand the validation behavior. Oh well.....


Thanks! - Rich

 

[Jonmo1]

DOES O89 Actually = "BC-7" at the time you are entering the formula?

Because if it doesn't then the formula equates to FALSE.

FALSE is an invalid entry for the Data Validation.
Try typing =FALSE in the Source box - doesn't work right?

There MUST be something there if List is selected in datavalidation.

Maybe make it point to a blank list on FALSE

=IF($O$89="BC-7",RGU_Type,A1:A10)


P.S.
No need to manually type FALSE in the False part of an IF formula. That's what it will return by default if the expression is false.

 

[Fujirich]

Thanks Jonmo1! As my previous post indicates, I stumbled upon the same thing but wasn't sure why. You explanation clears it up and helps me understand Excel's "rules".

Very appreciated!!