Data Validation and dynamic ranges

[gjadcock]

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

 

[Aladin Akyurek]

Not sure but you probably trying something similar to what I describe in:

http://www.mrexcel.com/board/viewtopic.php?topic=40&forum=2

 

[gjadcock]

thanks aladin, this is exactley what I have, however I need the range to be dynamic. If I add extra lines to the selection list I dont want to keep altering the range (more likely I'll forget, and spend hours trying to figure out whats wrong)

Using the counter etc to make the range dynamic seems to **** everyting up and the data validation no longer works.

I can't figure out why??

 

[Aladin Akyurek]

On 2002-09-18 09:20, gjadcock wrote:
thanks aladin, this is exactley what I have, however I need the range to be dynamic. If I add extra lines to the selection list I dont want to keep altering the range (more likely I'll forget, and spend hours trying to figure out whats wrong)

Using the counter etc to make the range dynamic seems to **** everyting up and the data validation no longer works.

I can't figure out why??

I suppose there is a dereferencing issue here...applying INDIRECT to INDIRECT.

Define Office instead as follows (if Office must refer to an area that dynamically changes -- that is, either crimps or expands)...

( 1.) Insert a worksheet in the workbook containing the sheet Room Types and name it Admin.
( 2.) Activate Admin.
( 3.) In A2 enter: # of rows [ which is just a label ]
( 4.) In B2 enter: =MATCH(REPT("z",90),'Room Types'!B:B)
( 4.) In A3 enter: # of data recs [ which is just a label ]
( 5.) In B3 enter: =B2-(CELL("Row",'Room Types'!B2)-1)
( 6.) In A4 enter: # of data columns [ which is just a label ]
( 7.) In B4 enter: 1 [ hardcoded number of columns in use ]
( 8.) Activate Insert|Name|Define.
( 9.) In the Names in Workbook box, enter Office as name.
(10.) In the Refers to box, enter the following formula:

=OFFSET('Room Types'!$B$2,0,0,Admin!$B$3,Admin!$B$4)

(11.) Click OK.