VBA to look at column A find a sheet and do a sum if from that shee

**jimstrongy** · 02-10-2012, 01:02 PM

I need a macro that will look at summary sheet column A and find a coresponding sheet with the same date. Go to that sheet and sum column E if column C cell has 1 or 2 in it.

Please see attached
Thank you

**Pete_UK** · 02-10-2012, 01:47 PM

You can put this formula in B14 of your summary sheet:

=SUMPRODUCT(--((INDIRECT("'"&TEXT($A14,"mm-dd-yy")&"'!C2:C10")=1)+(INDIRECT("'"&TEXT($A14,"mm-dd-yy")&"'!C2:C10")=2)),INDIRECT("'"&TEXT($A14,"mm-dd-yy")&"'!E2:E10"))

and then copy down. You might like to change the ranges C2:C10 (twice) and E2:E10 to suit the real extent of your data in those other sheets.

Hope this helps.

Pete

**jimstrongy** · 02-10-2012, 02:15 PM

Originally Posted by Pete_UK

You can put this formula in B14 of your summary sheet:

=SUMPRODUCT(--((INDIRECT("'"&TEXT($A14,"mm-dd-yy")&"'!C2:C10")=1)+(INDIRECT("'"&TEXT($A14,"mm-dd-yy")&"'!C2:C10")=2)),INDIRECT("'"&TEXT($A14,"mm-dd-yy")&"'!E2:E10"))

and then copy down. You might like to change the ranges C2:C10 (twice) and E2:E10 to suit the real extent of your data in those other sheets.

Hope this helps.

Pete

Thanks Pete but I really need the macro for this. Example provided is just a simple version of what needs to be cone