Lookup across multiple worksheets

I'm trying to code a large, complex workbook in a rather complicated way that will likely involve many steps.  I'm currently stuck on first step.  :)

The workbook comes from our headquarters, and is very large and very complex with lots of coding already embedded.  And the extremely unfortunate naming of worksheets with a comma included (endless problems with that one).  I can probably rename the sheet names without disrupting much in the workbook, but the rest of it is what it is.

The workbook is lots of information about sites that we support.  The last sheet in the workbook is called "Site Data", and has a LocationID (unique identifier) with lots of columns of information.  All our sites are listed on this sheet.  Then, there are 27 worksheets that sit between sheets called START and END that represent our 27 partners.  Our sites are spread across these sheets, depending on which partner works with that site (on these sheets, the LocationID is called iPSLID, but it's the same number and unique identifier).

We enter data on a weekly basis on the 27 partner sheets about audit visits happening at the sites.  I need to create a new sheet that shows all of our sites and then adds 7 columns from whichever of the 27 sheets that site is listed on.  In effect, I could copy the "Site Data" sheet exactly as is, then add 7 columns (shown on sheet called "SHEET I NEED" on attached as blue and purple) and have excel search the 27 worksheets for each site and fill in those 7 columns on the master sheet.

I've found code online that is supposed to do this, using an array formula with VLOOKUP, MATCH, INDEX, and COUNTIF.  But I can't get it to work.  Help appreciated!
cleared-sheet.xlsx
zalikAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
From your existing Site Data sheet, are you able to identify which of the 27 Site sheets the data will come from?

If you can identify the Sheet name related to a location ID, you could then use a combination of INDIRECT with VLOOKUP.

INDIRECT looks at the contents of a cell and then interprets the contents as a cell reference and returns the contents of the referred cell.

Likewise ADDRESS function can use a cell reference containing a sheet name.

Thanks
Rob H
tomfarrarCommented:
Is this a one time exercise or will you need to do this over and over?  

You could consolidate the 27 tabs by copying and pasting as link on a consolidated tab which should reflect changes to the data going forward (assuming no more location ID's are added to the 27 tabs) which may be a bad assumption.  With the consolidated tab you could probably manage with VLookUp.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zalikAuthor Commented:
@ Rob - Interesting.   But yes, on the Site Data sheet it does give me the partner name, which is how the sheet is named.  So I can identify in that way which of the 27 sheets the site is listed. I have to explore that...

@tomfarrar - the end result is that I am intending to create a cover worksheet that will list the sites that still need to have an audit visit, and give the end date that the visit must be done by.  So on the 27 sheets we enter whether the site is high volume or low volume (all high volume needs visits within the year), whether it is a new or continuing site (all new sites within the year), whether it already had a visit (falls off the list), or whether that visit showed poor performance (needs revisit within 6 months).  Etc.  It's currently too challenging for us to prioritize where we need to visit next, and which sites are reaching the deadline for a visit having to look across all the sheets.

So to combine those columns, as long as the data on the new master sheet updates as the information as the 27 sheets update, the consolidation is a one-time thing.  Then I will further code the master sheet for all of the combinations of possibilities, and then code what the due date is for the audit.  And, lastly, create a sorted list showing site name and due date in order.  There will be new sites added as we go along, although thinking about the copy and paste it's possible I could forecast a % of new sites that will be added and put dummy sites on the sheets.. hmmm.

These are good ideas for me to play around with...
zalikAuthor Commented:
The copy and paste thing with vlookup seems to be doing the trick.  Not very pretty, but as long as nobody but me has to figure out what's going on with this sheet, should work out just fine.  Thanks!
tomfarrarCommented:
Thanks, zalik.  I hope your people allow you to redesign the spreadsheet at some point.  It looks like a bear to work with.  - Tom
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.