formula will not fill correctly due to odd number rows  
Author Message
mlopat





PostPosted: Tue May 10 13:28:06 CDT 2005 Top

worksheet functions >> formula will not fill correctly due to odd number rows

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

Excel239  
 
 
Alok





PostPosted: Tue May 10 13:28:06 CDT 2005 Top

worksheet functions >> formula will not fill correctly due to odd number rows 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




> 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
 
 
Barb





PostPosted: Tue May 10 14:22:04 CDT 2005 Top

worksheet functions >> formula will not fill correctly due to odd number rows 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



> 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
>
>

>
> > 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
 
 
Barb





PostPosted: Tue May 10 15:10:12 CDT 2005 Top

worksheet functions >> formula will not fill correctly due to odd number rows 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



> 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
>
>

>
> > 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