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
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