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

Fujirich

Active Member
Joined
May 1, 2003
Messages
320
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Jan 4, 2007
Messages
876
Just a guess:

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

Fujirich

Active Member
Joined
May 1, 2003
Messages
320
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

Active Member
Joined
May 1, 2003
Messages
320
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

Active Member
Joined
May 1, 2003
Messages
320
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Active Member
Joined
May 1, 2003
Messages
320
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!!
 

Forum statistics

Threads
1,181,658
Messages
5,931,270
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top