Data Validation and dynamic ranges

gjadcock

New Member
Joined
Sep 17, 2002
Messages
27
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

gjadcock

New Member
Joined
Sep 17, 2002
Messages
27
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,144,052
Messages
5,722,242
Members
422,418
Latest member
Chipsy

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