Option to combine single row from many tables into one new table

[Larry of Oz]

Hello all,

I'm looking for advice on the best way to merge one row of data from multiple tables into one table. I am happy to look up and learn the process, but I'm a bit confused about which option is best so I can direct my learning.

Solution needed for:

1. Workbook containing 6 worksheets, each worksheet is for a different location
2. Each worksheet has a table with identical column headers e.g. 'Month', 'Year', 'Highlight', 'Concerns", 'Risks' and each worksheet is updated monthly by the staff member responsible for that location
3. Once a month a report is required for Executives that pulls the latest month's row from each worksheet resulting in a report for, say January 2024 that contains the January entries for each of the six locations/worksheets
4. It needs to be clean looking, e.g. a snapshot with no need to drill down into information

Is anyone able to suggest the best method to do this so I can focus my learning in that direction? I have already looked into it but am a bit confused about data model, power query or whatever else might be the best solution, even if that is just an Excel formula.

Many thanks in advance for any suggestions.
Lauren

 

[awoohaw]

If you know the sheet location , or get a copy of the sheets from the people and store it in your location Power Query can do the trick.
Take a look at this video:

It may not do exactly as you ask. But, you could probably figure out how to filter to get only the new records.

 

[Larry of Oz]

Awesome, thank you so much! I think this will do the trick. Appreciate the advice on which direction to explore, cheers!

 

[awoohaw]

You're welcome. Best wishes!