Use the technique found he

http://www.dailydoseofexcel.com/arch...ta-validation/

But use dynamic named ranges instead. They will auto-expand when new
information is added.

Column A defined name:
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)
Column B defined name:
=OFFSET($B$1,1,0,COUNTA($B:$B)-1,1)
Column C defined name:
=OFFSET($C$1,1,0,COUNTA($C:$C)-1,1)


--JP


On Sep 17, 8:15*am, gibbylinks
wrote:
Hi,

I've posted this before and was pointed to a website that did this, but used
named ranges, somthing I'm trying to avoid.

I have a spreadsheet with company names along the top row, with the contacts
for each company on the rows directly beneath each company name.

what I want is a drop down list to pick the company (easy enough) but then
another drop down to pick the contact from that company (hard bit).

So when you pick a contact you are limited to only the contacts for that
company.

I don't want to have to declare a range for each company, as I want it easy
for users to add there own companies and contacts.

Thanks