Setting up Drop Down Menu Source List from Different File

  • Thread starter Thread starter hailnorm
  • Start date Start date
H

hailnorm

How do I set up the drop down list to use data from a different exce
file?

I have about 40+ files with various drop lists that need to be linke
to a "master source file" which changes fairly regularily. There ar
various VLookup formulas also being utilized within the 40+ files.

Since the entries in the "master" file grows, is there way to make th
source range dynamic so that the drop down list & VLookup formula
adjust automatically?

Also, how would I use source data from another worksheet with the sam
file? Would hiding this "source worksheet" cause problems
 
Hi
have a look at
http://www.contextures.com/xlDataVal05.html
for data validation with lists from other workbooks (using defined
names). Note: The other workbook has to be opened to do this

For dynamic ranges you can use a combination of OFFSET + COUNTA (if you
have no blank rows in between). e.g.
=VLOOKUP(lookup_criteria,OFFSET('other_sheet'!$A$1:$B$1,0,0,COUNTA($A:$
A)),2,0)

Hiding a sheet should cause no problems
 
Back
Top