formula will not fill correctly due to odd number rows

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

Guest

I am using a formula to add sales in one workbook in the second book,
xxxx[2005 Sales 04.xls]Sheet1'!B9:B10), the problem is when I try to copy by
filling it assumes the rows are in order when in reality they are like this
b9:b10, b11:b12, b13:b14.... is there a way to do this, other wise I'll be
spending a lot of time editing.
Thank you and have a wonderful day
 
Hi Barb,
You have to revise the formula like shown below

=SUM(OFFSET([2005 Sales 04.xls]Sheet1!$B$9,(ROW()-1)*2,0,2,1))

The above assumes that the formula is being entered in row 1 (column does
not matter). The formula works because it offsets the fixed Cell B9 by two
rows each time you drag the formula down by one row and the size of the area
is determined by the second to the last parameter to the offset function.

HTH
Alok Joshi
 
It works if I delete the title row, but does not work when I change that 1 to
a 2. Hopefully my boss won't mind.

Thank you very much!
Have a wonderful day!
Barb

Alok said:
Hi Barb,
You have to revise the formula like shown below

=SUM(OFFSET([2005 Sales 04.xls]Sheet1!$B$9,(ROW()-1)*2,0,2,1))

The above assumes that the formula is being entered in row 1 (column does
not matter). The formula works because it offsets the fixed Cell B9 by two
rows each time you drag the formula down by one row and the size of the area
is determined by the second to the last parameter to the offset function.

HTH
Alok Joshi


Barb said:
I am using a formula to add sales in one workbook in the second book,
xxxx[2005 Sales 04.xls]Sheet1'!B9:B10), the problem is when I try to copy by
filling it assumes the rows are in order when in reality they are like this
b9:b10, b11:b12, b13:b14.... is there a way to do this, other wise I'll be
spending a lot of time editing.
Thank you and have a wonderful day
Click to expand...
Click to expand...
 
Thank you! Got it to work with row 2, I must really be brain dead today!
Thanks for helping me, I can get some sleep tonight!

Have a wonderful week!

Barb

Alok said:
Hi Barb,
You have to revise the formula like shown below

=SUM(OFFSET([2005 Sales 04.xls]Sheet1!$B$9,(ROW()-1)*2,0,2,1))

The above assumes that the formula is being entered in row 1 (column does
not matter). The formula works because it offsets the fixed Cell B9 by two
rows each time you drag the formula down by one row and the size of the area
is determined by the second to the last parameter to the offset function.

HTH
Alok Joshi


Barb said:
I am using a formula to add sales in one workbook in the second book,
xxxx[2005 Sales 04.xls]Sheet1'!B9:B10), the problem is when I try to copy by
filling it assumes the rows are in order when in reality they are like this
b9:b10, b11:b12, b13:b14.... is there a way to do this, other wise I'll be
spending a lot of time editing.
Thank you and have a wonderful day
Click to expand...
Click to expand...
 
Back
Top