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?
=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?