How do I change the Row Reference from a seperate worksheet.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created 8 worksheets within a workbook. Each worksheet with in the
workbook represent an employees data abstraction works.

So what I need to do is take the following:
=SUM(Amini!M$2+Blazer!M$2+DiSalvo!M$2+Hensel!M$2+Mengon!M$2+Rees!M$2)

And change the Column up to N, O, P....etc:
e.g.
=SUM(Amini!N$2+Blazer!N$2+DiSalvo!N$2+Hensel!N$2+Mengon!N$2+Rees!N$2)

I can't find a way to up the column as I drag to update the column.

Thank you in advance!!!
 
I presume you are dragging DOWN your sheet with that formula? If so, then
try this, replace each of your Sheet!M$2 references to an Offset value like
this (just showing 2 values, repeat for all)
=SUM(Offset(Amini!M$2,0,ROW()-3)+Offset(Blazer!M$2,0,ROW()-3)+....)
By the way, when you use + within a SUM, it is extraneous, your SUM
statement can be written as:
=Amini!M$2+Blazer!M$2+DiSalvo!M$2+Hensel!M$2+Mengon!M$2+Rees!M$2
with the same results. Or if you want to still use SUM, then this give same
results:
=SUM(Amini!M$2,Blazer!M$2,DiSalvo!M$2,Hensel!M$2,Mengon!M$2,Rees!M$2)
again same results, and actually slightly faster.

But back to the Offset() issue. the "-3" value I show in my example should
be the row number of the first row where you want the total of column M. So
the presumption would be that my example formula would be somewhere in row 3
of some sheet. As the formula is dragged down the sheet, the value of ROW()
increases, but always subtracts first row number, so the column offset
increases by one for each row you drag it down the sheet.
 
Back
Top