Automation help

Hi, i am a complete newbie to any type of macros or automation

The task i would like to do is in the attached file, basically its this.

The first 5 sheets are data from specific sites and volumes, the last sheet is a table whereby i would like it to extract the information from the first 5 sheets and compile it into the table in the relevant format. i can do formula to do the first row of data but how do i get it to step down to the next row and populate the table exactly the same but with the relevant data...

The data is from supermarkets and the store numbers are sites...essentially its the same formula but repeated 500 times by row?

In the final tab, the rest of the information is built with basic formula to auto calculate when data is entered to the formatted table

Make any sense at all...thanks for your help, much appreciated

Sorry, my explanation is rubbish.

The table in the "Table" Tab would be a host for the data to be calculated by store and inserted into. I would then need that saving to be able to view the table by store once all have been calculated. The end game is to populate the table for each store based on the days and areas "grocery" etc. The ability to have this stored and emailed to each store is the end game? Hope that is clearer

If you take a look at the data in the table, this represents the data from store 1 in all tabs..i need it to calculate this for all stores and create a table for each.

ok so i have got the formula above to work for store 1....works perfect, now the question is: How do i get this to do exactly the same for store 2 onwards...the formula i adapted is


=SUMPRODUCT((INDIRECT("'" & $C3 & "'!C3:I3")=D$2)*(INDIRECT("'" & $C3 & "'!c$5:i5")))

Awesome thanks. I now need to attach each table to an email sender. The email addresses only differ by store number so: store1@xxx.co.uk -store2@xxx.co.uk and so on. Is this possible in this format?

Many thanks again