| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying formula
I have a formula in a worksheet with two sheets, A and B
The formula is in SheetA =SheetB!G8-SheetB!G7+SheetB!G9+SheetB!G10 How can I copy this formula down in SheetA but always getting information 13 rows down in SheetB and then again 13 rows down? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copying formula
=OFFSET(SheetB!$G$8,(ROW(A1)-1)*13,0)-
OFFSET(SheetB!$G$7,(ROW(A1)-1)*13,0)+ OFFSET(SheetB!$G$9,(ROW(A1)-1)*13,0)+ OFFSET(SheetB!$G$10,(ROW(A1)-1)*13,0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "MKH Eyrbakk" wrote in message ... I have a formula in a worksheet with two sheets, A and B The formula is in SheetA =SheetB!G8-SheetB!G7+SheetB!G9+SheetB!G10 How can I copy this formula down in SheetA but always getting information 13 rows down in SheetB and then again 13 rows down? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copying formula
Something is still wrong . What does (ROW(A1)-1) mean? The formula is in
SheetA. "Bob Phillips" wrote: =OFFSET(SheetB!$G$8,(ROW(A1)-1)*13,0)- OFFSET(SheetB!$G$7,(ROW(A1)-1)*13,0)+ OFFSET(SheetB!$G$9,(ROW(A1)-1)*13,0)+ OFFSET(SheetB!$G$10,(ROW(A1)-1)*13,0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "MKH Eyrbakk" wrote in message ... I have a formula in a worksheet with two sheets, A and B The formula is in SheetA =SheetB!G8-SheetB!G7+SheetB!G9+SheetB!G10 How can I copy this formula down in SheetA but always getting information 13 rows down in SheetB and then again 13 rows down? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copying formula
On the first occurrence of the formula, (ROW(A1)-1) will return 1 from the
ROW() function, as the row number for cell A1 is 1, and thus (ROW(A1)-1) will give zero, hence the offset function will off set by zero rows from the base address given. When you copy that down a row, the formula becomes =OFFSET(SheetB!$G$8,(ROW(A2)-1)*13,0)- OFFSET(SheetB!$G$7,(ROW(A2)-1)*13,0)+ OFFSET(SheetB!$G$9,(ROW(A2)-1)*13,0)+ OFFSET(SheetB!$G$10,(ROW(A2)-1)*13,0) In this case the row number for cell A2 is 2, so (ROW(A2)-1) gives 2-1 which is 1. This is multiplied by the 13 to offset each cell reference down by 13 rows, which is what you asked for. It is not very informative to say "something is wrong". We can't see over you shoulder so you'll need to tell us what the problem is. -- David Biddulph "MKH Eyrbakk" wrote in message ... Something is still wrong . What does (ROW(A1)-1) mean? The formula is in SheetA. "Bob Phillips" wrote: =OFFSET(SheetB!$G$8,(ROW(A1)-1)*13,0)- OFFSET(SheetB!$G$7,(ROW(A1)-1)*13,0)+ OFFSET(SheetB!$G$9,(ROW(A1)-1)*13,0)+ OFFSET(SheetB!$G$10,(ROW(A1)-1)*13,0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "MKH Eyrbakk" wrote in message ... I have a formula in a worksheet with two sheets, A and B The formula is in SheetA =SheetB!G8-SheetB!G7+SheetB!G9+SheetB!G10 How can I copy this formula down in SheetA but always getting information 13 rows down in SheetB and then again 13 rows down? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copying formula
Thank you very much for your assistance. Now all is working.
Best regards, MKH "David Biddulph" wrote: On the first occurrence of the formula, (ROW(A1)-1) will return 1 from the ROW() function, as the row number for cell A1 is 1, and thus (ROW(A1)-1) will give zero, hence the offset function will off set by zero rows from the base address given. When you copy that down a row, the formula becomes =OFFSET(SheetB!$G$8,(ROW(A2)-1)*13,0)- OFFSET(SheetB!$G$7,(ROW(A2)-1)*13,0)+ OFFSET(SheetB!$G$9,(ROW(A2)-1)*13,0)+ OFFSET(SheetB!$G$10,(ROW(A2)-1)*13,0) In this case the row number for cell A2 is 2, so (ROW(A2)-1) gives 2-1 which is 1. This is multiplied by the 13 to offset each cell reference down by 13 rows, which is what you asked for. It is not very informative to say "something is wrong". We can't see over you shoulder so you'll need to tell us what the problem is. -- David Biddulph "MKH Eyrbakk" wrote in message ... Something is still wrong . What does (ROW(A1)-1) mean? The formula is in SheetA. "Bob Phillips" wrote: =OFFSET(SheetB!$G$8,(ROW(A1)-1)*13,0)- OFFSET(SheetB!$G$7,(ROW(A1)-1)*13,0)+ OFFSET(SheetB!$G$9,(ROW(A1)-1)*13,0)+ OFFSET(SheetB!$G$10,(ROW(A1)-1)*13,0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "MKH Eyrbakk" wrote in message ... I have a formula in a worksheet with two sheets, A and B The formula is in SheetA =SheetB!G8-SheetB!G7+SheetB!G9+SheetB!G10 How can I copy this formula down in SheetA but always getting information 13 rows down in SheetB and then again 13 rows down? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copying the Hyperlink function result without copying the actual formula | Excel Worksheet Functions | |||
| Formula not copying down | Excel Worksheet Functions | |||
| Help copying formula | Excel Discussion (Misc queries) | |||
| copying a formula | Excel Discussion (Misc queries) | |||
| copying formula down | Excel Discussion (Misc queries) | |||
Linear Mode
