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!