Expert: Miguel Zapico - 10/20/2009

Question
How do I have a drop down list display ranges of data from other worksheets. I want to have a master sheet with the dropdown that displays tables of equipment and specs on that equipment. The dropdown will list the names of Firefighters in my company. Once you choose the Firefighter's name from the dropdown I want to see the equipment table data populate the table on the master sheet. Each Firefighter has his own worksheet with the same table on it containing the data.

Answer
You can use the data validation techniques from the following pages:
http://www.contextures.com/xlDataVal01.html
http://www.contextures.com/xldataval02.html
The first one is focused on the basis of data validation, with the introduction of named ranges, and the second sets up an example with dependent list.  Even if they may not fit 100% with your requirements, they may give you some ideas on how to use Excel drop downs.

Regarding bring data based on a sheet name, you may check the help for the function INDIRECT.  This and the previous articles could help you in setting up your own solution.

Hope this helps,
Miguel