Two Questions

[msfatman]

I'm using this formula to calculate data based over a 10 week period:-

=COUNTIF(Week1!J:J,H2)+COUNTIF(Week2!J:J,H2)+COUNTIF(Week3!J:J,H2)+COUNTIF(Week4!J:J,H2)+COUNTIF(Week5!J:J,H2)+COUNTIF(Week6!J:J,H2)+COUNTIF(Week7!J:J,H2)+COUNTIF(Week8!J:J,H2)+COUNTIF(Week9!J:J,H2)+COUNTIF(Week10!J:J,H2)

Can the formula be simplified?

Secondly, I need a macro to run when the cell contents of cells D1, F3 or G4 change. Could some one advise how I can do this?

 

[MRA]

well, I'm not quite sure how to simplify the code you have, but I can answer your second question.
Use the worksheet change event, with an intercept condition:

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("[your range here]")) Is Nothing Then
{code}
end if

that should do the trick

Mike

 

[Scott Huish]

I think this will work for your formula:

=SUMPRODUCT(COUNTIF(INDIRECT("Week"&ROW(INDIRECT("1:10"))&"!J:J"),H2))

 

[cornflakegirl]

Is there a reason why you can't keep all the data on the same worksheet (maybe with an additional field specifying the week)? If you need to analyse it all together, it would make life easier.

 

[Peter_SSs]

Another option. Assuming ...
a) the sheets Week1 to Week10 are consecutive sheets in the workbook.
b) cell K1 (any other cell will do) is vacant on each 'Week' sheets
c) the result sheet is called 'Summary'

... then try this.

1. Select Week1 sheet, then hold 'Shift' and select Week10 sheet. This should select all the 'Week' sheets
2. Select cell K1 and enter the formula
=COUNTIF(J:J,Summary!H2)
This should enter that formula on each of the 10 sheets.
3. Now select the 'Summary' sheet and enter the formula:
=SUM(Week1:Week10!K1)


Edit: Also, the suggested vba code to run when cells D1, F3 or G4 change, will only work if those cells are changed manually. If they contain formulas then you would need some code in the WorkSheet_Calculate event

 

[msfatman]

Thanks everyone that's made things a lot simpler.