Sharepoint Workbook - Pulling data off Multiple worksheets (wildcards in sheet title) within workbook, VLOOKUP to pull data, compare dates when pulling

Question

Hi all

In Sharepoint I have a Workbook which consists of many worksheets (one per calendar month so the names always change) plus a summary, or tracker sheet. The tracker sheet should be straightforward to autofill but I just can't figure out the layers of PowerQuery (does this still not work in Sharepoint?), IFERROR, VLOOKUP, XLOOKUP, ISNUMBER and all of the other things. Can someone clear the muddy waters a little please?

Each of the monthly worksheets (named Audit yymm) is filled out by various staff. At the end of the calendar month that sheet is protected and a new sheet is started. Each worksheet is preformatted name (Column A), date (Column D) and an automatically calculated score (Column C)

The tracker sheet (same workbook) pulls the name of each person from a master name sheet to Column A, (no issue there) then along that person's row are 6 more columns - DATE SCORE DATE SCORE DATE SCORE . The tracker sheet's job is to find the 3 most recent audits in the worksheets, return the date (Column D from the worksheet) and then the score (Column C from the worksheet). Obviously Column B&C on the tracker sheet work together (the first DATE SCORE) and once I've got a function working for each of those I then need to look for the next oldest audit to completed columns D&E and then again for F&G. At the end the score needs averaging, but I believe I can figure that one out :D .

I'm going round in circles with trying to work out the most efficient way to compare worksheet names (partial match to accommodate changing yymm) then VLOOKUP for the date and VLOOKUP for the score, before moving on to the next two audits for each person.

If anyone can give me any guidance as to how to compile the correct formulas (formulae?) on the tracker sheet I would honestly be really grateful. I've never had to ask for help before so am cap in hand and grateful for your time. Thank you in advance !

Answer 1

I would like to help you check the issue. Without a sample file, it is hard for me to share any suggestion on this. Can you upload it to OneDrive or any other cloud drive for investigation and then post the link here.

*Please make sure you have removed any sensitive or private information in the sample file before uploading.