VLOOKUP into multiple worksheets based on cell values.

[Tomo1971]

Hi, looking for some help - I am at a basic level at excel, have to use the formula wizards for vlookups and generally use google to find an answer and use trial and error to find a solution. To this question though, cant find anything,

We run a team of engineers that work over several projects in the week but we want to send one excel schedule out to them.

Each project uses their own excel project tracker and has their own information that needs entering on the schedule.

Each site they visit has a 5 digit unique code - the site reference. We want to extract, from different project trackers, the address / postcode etc for each engineer and day.

If it was just one project, simple VLOOKUP would suffice - we enter a site number and the VLOOKUP goes and fetches that info from the source. Is that attainable if we have several sources by using the same formula in each Day?

We could obviously enter on the schedule the site number and the project name (ie, 12345 & Project_Alpha)

So we would have multiple project trackers with the below info in them, probably different columns and want to transfer it into a schedule based on site number and project name

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Site Ref[/TD]
[TD]Address[/TD]
[TD]Postcode[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345[/TD]
[TD]25, High Street[/TD]
[TD]KW1 1QL[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]67890[/TD]
[TD]8, Low Street[/TD]
[TD]ST2 8PR[/TD]
[/TR]
</tbody>[/TABLE]

Can this be done?

 

[RasGhul]

You could use an indirect formula to pull data from different sheets but I think you would need a common source folder for the Engineers Workbooks.

Lets say for example that 10 engineers were saving their own sheets to one cloud folder such as Dropbox or google docs, then the file path source of the different sheets would be the same but just different Sheet names. Then you could just have a table on your schedule sheet that has the site number and workbook file name which the formula would use to lookup/extract your data.