INDIRECT and named ranges

**Excel_Learner42** · 01-02-2009, 11:18 AM

I have two cells that contain dropdown boxes. Cell A1 has a list of departments (HR, IT, Accounting, etc.) and Cell A2 uses the INDIRECT function to pull in a list of positions applicable to the selected department in A1 using named ranges.

I know how to do dynamic ranges if I have each list of department positions in different columns, but my source sheet has hundreds of them in one column (for example a1:a50 might be HR department, with b1:b50 being 50 HR positions, then a51:a200 might be IT with b51:B200 being IT positions). Someone updating the chart might insert a row in the middle somewhere to add a new IT position.

What I'm wondering is how to make my nested dropdown of positions expand to include any new additions on the source sheet without making someone redefine the named range. Is this possible? Thanks much!!

**Macdave_19** · 01-02-2009, 11:46 AM

I would pivot that column mate then create your dynamic named range based on the data in that pivot.

i'd also add a refresh all pivots in the workbook_open module and on the worksheet_activate module that way your book will always be up to date.

hope that helps

Please Login or Register  to view this content.

Please Login or Register  to view this content.

HTH

**clownfish** · 01-02-2009, 01:17 PM

I'm not sure if this will get you on your way or not, but you can create a dynamic range by the following method-- if your department name is in Col A and the positions are in Col B, make a list of all departments (assuming just a few, you can do this by hand) somewhere that you've got space (say, Col D), and in the column next to them, create a dynamic range for each department like this:

="B$"&MATCH(D1,A:A,0)&":B$"&MATCH(D1,A:A,0)+COUNTIF(A:A,D1)-1

You can then refer to this range in an INDIRECT formula within a more complex formula. This will keep accurate ranges even if someone inserts a row in the middle somewhere. Note that this assumes your positions are sorted by department.