Excel Vlookup that will reference multiple worksheets

[MGHenry]

I have a workbook made up of approximately 17 worksheets.

The first worksheet is the form that displays all the data.

the last 15 worksheets contain the jobs that 15 individuals have opened. This data is brought over from a separate workbook using a series of Macros.

What I want to do on the first worksheet is have a pull down menu with the names of the people opening jobs and have a v lookup go to the multiple work sheets and find the jobs that this person has opened and return them to allow another drop down box to pull up the number and then have vlookup go back to the work sheet and pull up the job description.

I have thought as an alternate to only have one job data worksheet with all the jobs and the peoples names on it and filter by the results of the pulldown but how do I then allow the user to pulldown all the associated job numbers ?

Thanks in advance for your help.

 

[SkipVought]

Hi,

[quote[the last 15 worksheets contain the jobs that 15 individuals have opened. This data is brought over from a separate workbook using a series of Macros.[/quote]

You are one step short of complete.

You ought to combine the data from all 15 sheets into ONE table (sheet)/ MS Query is one option faq68-5829.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

agreed - vlookup over 15 sheets = a right royal pain!!

Would really either need code or aggregation (as per Skip)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MGHenry]

Thanks, I was thinking the same thing as well and it would just be a matter of changing the macros that load the sheet to get it all on one sheet.

However the issue remains,how do I make the second pulldown access only the jobs on the list that are associated with the job writer I have specified in the first pull down.

Thanks.

 

[AnotherHiggins]

See thread68-1469736: Dynamic data validation. There are a couple of links in my first post, then Skip and I flesh out the idea.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Actually, I'd suggest you consider a Pivot Table. You (or the user) can pick a name from a list and the table will dynamically update.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.