Pulling data from other wb based on data to populate dropdown list
 

Sorry if this is too long-winded, but I'm trying to explain something I
wasn't able to do a help search on using general terms. I am setting up a
workbook that will be sent out to regional directors during budget times.
Basically this workbook will be a last chance for the directors to make
changes and to see how these changes will affect the next tier of our org
structure. We have almost 550 stores in 10 states. Each store is assigned
to an area, that is in turn assigned to a zone, that is assigned to a region
and finally to a division. Since there are so many components of the budget
process, the source data is in separate very large files that rely heavily on
drop-down lists to change the data in the tabs. I'm using a combination of
the VLookup and HLookup functions along with basic links to the other
extremely large workbooks to populate this workbook and keep it within email
limits. I have an area tab that will populate the data based on the value in
the dropdown list. Since I'm trying to keep this template file small enough
to email to the respective directors (they know they are not to hit the
Update button that pops up), I rely on the dropdown boxes quite a bit. In
fact my problem is that we rely on drop down boxes on the majority of the
spreadsheets. That's where my confusion starts. I need to bring over the
value in a cell in another spreadsheet that is based on the value of a
drop-down list. For example, Store 31 is located in Region 8000, Zone 8400,
Area 8450. Since the alignment is subject to change, I have a lookup table
that gives me the current configuration. One bit of data is in the balanced
scorecard workbook. The tab that will have my information is based on a
dropdown box. So, to pull store 31's scorecard data, I first have to select
Area 8450 from the dropdown box. The spreadsheet will then pull up all the
units in that area and give their data. How do I pull data from this
spreadsheet into my template workbook since the unit I'm looking for is
dependent on the value in a dropdown list? Would I do it by a formula or
through code? If code, what is the best way to structure it? Thanks for any
help you can provide on this long-winded post :-).