Creating a control sheet from multiple sheets

Hi all and thank you for looking.

Please see attached document which shows individual sheets with weighbridge information for loads that individual suppliers have delivered on a weekly basis. This information is taken from the weighbridge and copied from a csv file into the document. I would like to be able to corroborate the weekly sheets into the Control Sheet without having to amend the information on the weekly sheets.

On the control sheet I have filled in weeks 1-5 with the information that should be present.

I regularly use =sumproduct((INDIRECT("'"&C$1&"'!$N$2:$N$325")<>"")*(INDIRECT("'"&C$1&"'!$N$2:$N$325")={0,1,2,3,4,5,6,7,8,9})*(INDIRECT("'"&C$1&"'!$E$2:$E$325")=$A5)*(INDIRECT("'"&C$1&"'!$G$2:$G$325")=$B5)) and various Sum** versions but I can't quite alter it to make this (more simple!) one work.

In the past GaryBarlow, Special K and Glenn Kennedy have been very helpful.

All help greatly appreciated

Cheers,

Phil

On 'Control Sheet':
Enter formula for cell B2 and copy across:

Please Login or Register  to view this content.

Enter formula for cell B3 and copy across:

Please Login or Register  to view this content.

Hi cyiangou,

That works a treat and I'm very grateful, thank you.

As an added axtra, please can you advise how to avoid #REF! in the cells where sheets are yet to be created so that the Total tonnage is calculated?

Thank you

Just wrap each of those formulas in an IFERROR function:
So,

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

Sorted, wrapped it with IFERROR.

=IFERROR(SUMIFS(INDIRECT("'"&B$1&"'!$H:$H"),INDIRECT("'"&B$1&"'!$B:$B"),">0"),0)

Ah good. I saw that you have Excel 2003, so I was working on a non-IFERROR alternative, thinking it didn't exist yet.

Thanks for your help.

My pleasure, thanks for the rep.