Formula Editing in vba

[k_v_deepu]

Hi,

I have a excel workbook which has detail of capacity planning on a weekly basis, each week has 2 worksheets (Ex: "Weekly 1Sep-5Sep" & "Weekly Indv 1Sep-5Sep"
In "Weekly Indv 1Sep-5Sep" sheet i update individuals daily workload in hours and i calculate the total for the week in "Weekly 1Sep-5Sep" sheet by the formula
=SUM('Weekly Indv 1Sep-5Sep'!C4+'Weekly Indv 1Sep-5Sep'!M4+'Weekly Indv 1Sep-5Sep'!W4+'Weekly Indv 1Sep-5Sep'!AG4+'Weekly Indv 1Sep-5Sep'!AQ4+'Weekly Indv 1Sep-5Sep'!BA4+'Weekly Indv 1Sep-5Sep'!BK4+'Weekly Indv 1Sep-5Sep'!BU4+'Weekly Indv 1Sep-5Sep'!CE4+'Weekly Indv 1Sep-5Sep'!CO4+'Weekly Indv 1Sep-5Sep'!CY4)

now i have a new recruitee in my team so i have edit the above formula
and add the new recruitees time also, the formula should be like below
=SUM('Weekly Indv 1Sep-5Sep'!C4+'Weekly Indv 1Sep-5Sep'!M4+'Weekly Indv 1Sep-5Sep'!W4+'Weekly Indv 1Sep-5Sep'!AG4+'Weekly Indv 1Sep-5Sep'!AQ4+'Weekly Indv 1Sep-5Sep'!BA4+'Weekly Indv 1Sep-5Sep'!BK4+'Weekly Indv 1Sep-5Sep'!BU4+'Weekly Indv 1Sep-5Sep'!CE4+'Weekly Indv 1Sep-5Sep'!CO4+'Weekly Indv 1Sep-5Sep'!CY4+'Weekly Indv 1Sep-5Sep'!DI4)

i have sheets upto december of this year on a weekly basis

can anyone suggest me a code to alter the formula in each "B4" cell of each Weekly sheet

thanks in advance

[Bob Phillips]

Change the formula to something more dynamic

=SUMPRODUCT(--(MOD(COLUMN('Weekly Indv 1Sep-5Sep'!4:4),10)=3),'Weekly Indv 1Sep-5Sep'!4:4)

[k_v_deepu]

no it is not giving me the required result, can u please suggest me a different formula or a code wherein i can edit the formula to add the new cell to be added across all the sheets

[Bob Phillips]

No I can't. Can you tell me in what way it doesn't give the required result.

[k_v_deepu]

the output of the earlier formula is 0, whereas when i put the formula given by u it is giving me 18.8 as output

[Bob Phillips]

What is the point of a formula that gives 0?

It would be more helpful if you could explain why one gives an answer, what data generated that result. I can't read your mind.

[k_v_deepu]

i am sorry, formula u suggested is working fine.
In your formula u r reffering the whole row i,e. 4:4, whereas my formula takes only 10 cells which i have mentioned in the formula there was a value 18.8 in a column DI which was not referred in my earlier formula

thankyou very much