Complete Summary Table with data from multiple sheets

[pioshelby1980com]

Hi and happy new year!!!

I am looking to populate a summary sheet from a series of sheets within the same workbook.

The summary sheet has the values completed above in cells D6 to G8. I am looking to be able to run a routine that will scan the other sheets in the workbook and complete the summary table. The number of sheets can change as time goes on and more get added.

The first sheet above contains the data in cells L30 to O33. This can change over time and in each sheet as more sections get added to the sheet. The format remains the same though so the cells can be found relative to those that contain the strings located in cells D30 to D33. Can the "Templates" sheet be excluded?

The number of sheets can change over time so I was thinking about a routine that can be run every time someone adds a new sheet. I think that is the simplest way of doing it.

Below is a link to the file on Dropbox.
Project Costs_Working_File BD 1.xlsm

I would appreciate your help with this please.

Regards,
B.

 

[jkpieterse]

I would use Data, Get Data, From File, From Workbook for this.

 

[pioshelby1980com]

Thanks Jan, but I am looking for a simpler way to do it as those using it do not have the technical ability to complete that.

 

[jkpieterse]

You could set it up so the user would never have to touch it, just save and click refresh all.

 

[pioshelby1980com]

Thanks Jan but to do it I would have to reformat all of my tables.

 

[jkpieterse]

I don't see why? Power Query is perfectly capable of getting your data from the numbered sheets into the right shape.

 

[pioshelby1980com]

I tried that and was not able to get it to work.