Displaying data based on drop-down menus (Index/Match help needed)

**taketwo** · 09-28-2009, 06:44 PM

I hope I can explain this.

I have a very large database, filled with every job we're working on and all the details about it - duties, employees, vehicles, material-costs, etc.

I am currently working on a 1-page "Summary Sheet", that would be used to wade through all of this info and only give me the information I am looking for on one job. I would like to be able to provide drop-down menus on this "Summary Sheet" so that management can pick the information they'd like to see, and not be bombarded by everything else.

Ideally, what I am trying to do is to have a space on top where somebody could enter the name of the job, and then when they pick an item from the drop-down list, it would pull that info from the database via an "Index/Match". When somebody enters a job-name and picks "Joints Tested" from the drop-down, it will only display how many joints were tested on that job.

I already have the drop-downs created, and have other Index/Match formulas in other spreadsheets (they trip me up a bit, but I can generally fiddle with them until they work correctly), but can't figure out how to make it work this way. My database has headings above each column, and I'm writing those same headings next the different options in the drop-down list, but I can't figure it out.

Thanks in advance.

**Palmetto** · 09-28-2009, 06:54 PM

Can you post a sample workbook that demonstrates the desired results?
Seems like this could be a job for an automated Advanced Filter, but it a precise answer can only give by looking at a sample.

**taketwo** · 09-28-2009, 07:20 PM

Ok, I've attached a sample workbook.

There are two tabs, one representing the Database, and one representing the Summary-Sheet.

What I'm looking to do is, on the Summary-Sheet, enter a job-number on top (in this case, "Atown", "Btown", or "Ctown"), and then click one of the drop-down menus ("Duties"), with the result next popping up next to it.

For instance, if I enter job-name "Atown", and pick "Clean Stuff", I want "3" to pop up next to it.

Thanks a lot.

**teylyn** · 09-28-2009, 07:39 PM

Hi,

have a look at the attached. I've created the range name "jobs" on the Database tab to use it in the data validation list on the Summary tab.

Also some suggestions on the range name for the Duty data validation (see inside the workbook).

Your formula basically is:

=IF(ISBLANK(B5),"",VLOOKUP($B$2,Database!$A$3:$D$7,MATCH(Summary!B5,Options,0)+1,FALSE))

This will work as long as there is only one row per job in the Database.

Hope that helps

**taketwo** · 09-28-2009, 08:58 PM

Thank you so much, any look-up function other than "vlookup/hlookup" confuses me.

I do have one problem, though: there's no way I'm going to be able to get those headers in the same order on both the "Summary" and "Database", too many different ways of verbalizing the different duties, employees, etc.

Would it be possible to create a formula where the headers (above the columns and next to the drop-down) options do not have to be in the same order? Possibly via some sort of "index/match" function?

Sorry if I seem ungrateful, I'm just on a tight deadline; I appreciate all the help this forum has given me over the last few months.

**teylyn** · 09-28-2009, 10:03 PM

Hi taketwo,

you currently have the range name "options" defined as Summary!E3:E5. If you change the definition for "Options" to be Database!$B$2:$D$2, you won't have to worry about maintaining two lists, because then VLOOKUP uses the MATCH to find the column for the lookup in the Database table directly.