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


"Barb" wrote:

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

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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