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