Macro for linked drop down boxes

[Jennifer]

I have inherited an excel file and need help to modify it. On sheet one is a
drop down box with the input range coming from sheet 2 and the cell link
writes to sheet 2. I cannot change the cell link because of the many
calculations dependent on that value being written to sheet 2 (indexed).

The problem is that the drop down list has 800 entries. I want to add
another drop down box to allow the user to select a value in the new drop
down box to then limit the entries inthe existing drop down box. The problem
is that the list of 800 must stay in the same column in sheet 2 and the drop
down boxes must stay on sheet 1.

So how do I say if drop down box 1 value selected is 'x' then drop down box
2 should display a1 through a30 else if drop down box 1 value selected is
'y' then drop down box 2 should display a31 through a36 and so on. It seems
that because the drop down boxes are on one sheet and the values are on
another sheet this becomes more difficult.

I've been advised to use data validation but wouldn't that mean I would have
to move the information into separate columns? And I cannot do this. I need
to know if it is possible to write a macro that handles the relationship
between the two drop down boxes and suggestions on how to do so.

Thanks!


Regards,

 

[Bob Phillips]

Jennifer,

I have a sample workbook. Mail me if you want a copy.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)