hi all
i have 31 sheets in my excel file and i want to sum the results from this function when applied on each sheet
OK to be more clear i have values of 1 to 31 written in cells from M6 to AQ6
i used to sum the formulas by repeating them and change the $M$6 to $N$6 and "1" to "2" .... etc
example:
=IFERROR(VLOOKUP($C14,OFFSET(INDIRECT($M$6&"!"&ADDRESS(MATCH($C14,'1'!$C:$C,0),3,4,1)),,,,100),MATCH(G$12,OFFSET(INDIRECT($M$6&"!"&ADDRESS(MATCH($C14,'1'!$C:$C,0),3,4,1)),,,,100),0)+1,FALSE),0)
+IFERROR(VLOOKUP($C14,OFFSET(INDIRECT($N$6&"!"&ADDRESS(MATCH($C14,'2'!$C:$C,0),3,4,1)),,,,100),MATCH(G$12,OFFSET(INDIRECT($N$6&"!"&ADDRESS(MATCH($C14,'2'!$C:$C,0),3,4,1)),,,,100),0)+1,FALSE),0)
+......etc up to 31 sheets
it's very long and painful method to sum and it takes a lot of time if i want to edit it
so is there any way i can array this function across the 31 sheets in one move ?
and i'm sorry to say i don't need macros
thanks in advance
have a nice day
i have 31 sheets in my excel file and i want to sum the results from this function when applied on each sheet
OK to be more clear i have values of 1 to 31 written in cells from M6 to AQ6
i used to sum the formulas by repeating them and change the $M$6 to $N$6 and "1" to "2" .... etc
example:
=IFERROR(VLOOKUP($C14,OFFSET(INDIRECT($M$6&"!"&ADDRESS(MATCH($C14,'1'!$C:$C,0),3,4,1)),,,,100),MATCH(G$12,OFFSET(INDIRECT($M$6&"!"&ADDRESS(MATCH($C14,'1'!$C:$C,0),3,4,1)),,,,100),0)+1,FALSE),0)
+IFERROR(VLOOKUP($C14,OFFSET(INDIRECT($N$6&"!"&ADDRESS(MATCH($C14,'2'!$C:$C,0),3,4,1)),,,,100),MATCH(G$12,OFFSET(INDIRECT($N$6&"!"&ADDRESS(MATCH($C14,'2'!$C:$C,0),3,4,1)),,,,100),0)+1,FALSE),0)
+......etc up to 31 sheets
it's very long and painful method to sum and it takes a lot of time if i want to edit it
so is there any way i can array this function across the 31 sheets in one move ?
and i'm sorry to say i don't need macros
thanks in advance
have a nice day
Last edited:
