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