I have created a list of room types based on room category, I have a worksheet with each room type listed by category (One column per category)e.g. column b lists all the choices for the category "Office"
I have created a dynamic range name (this one is called office) for each column using the following formula in the "refers to" field under define name:
=indirect("'Room Types'!$b$2:$b$"&office_count)
office_count refers to a range that contains a counter which counts the number of entries in the column using
=counta(B:B)
In another worksheet I have a data validation drop down box which uses
=office.
This all works OK
However
if I change the criteria in the data validation to look at a cell which says office using the =indirect(e5) formula
the data validation does not work.
What I basically need is the user to select the room category from one cell and get a restricted list for room type in the next cell based on the room category selected.
Any ideas
I have created a dynamic range name (this one is called office) for each column using the following formula in the "refers to" field under define name:
=indirect("'Room Types'!$b$2:$b$"&office_count)
office_count refers to a range that contains a counter which counts the number of entries in the column using
=counta(B:B)
In another worksheet I have a data validation drop down box which uses
=office.
This all works OK
However
if I change the criteria in the data validation to look at a cell which says office using the =indirect(e5) formula
the data validation does not work.
What I basically need is the user to select the room category from one cell and get a restricted list for room type in the next cell based on the room category selected.
Any ideas