|
copying formulas
I am trying to copy formulas in a spreadsheet that pulls the data from every
4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, n6). When I try to copy the pattern to the next 4 cells in the row, anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I correct this? |
One way
=OFFSET(Sheet2!$B$6,,COLUMN(A:A)*4-4) copy across Regards, Peo Sjoblom "Tracey" wrote: I am trying to copy formulas in a spreadsheet that pulls the data from every 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, n6). When I try to copy the pattern to the next 4 cells in the row, anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I correct this? |
Hi
in the first cell (lets assume A1) enter =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4) and copy to the right -- Regards Frank Kabel Frankfurt, Germany "Tracey" schrieb im Newsbeitrag ... I am trying to copy formulas in a spreadsheet that pulls the data from every 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, n6). When I try to copy the pattern to the next 4 cells in the row, anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I correct this? |
Thank you. The OFFSET command identifies the correct cell. However, when I
copy to the right, the pattern doesn't repeat. It always remains anchored to the first cell (eg. A1), so all subsequent results repeat the same value. I tried removing the $ before the column, but it still doesn't work. I need the copied formula to move the anchor point over by 4 columns as the new reference. How do I do that? "Frank Kabel" wrote: Hi in the first cell (lets assume A1) enter =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4) and copy to the right -- Regards Frank Kabel Frankfurt, Germany "Tracey" schrieb im Newsbeitrag ... I am trying to copy formulas in a spreadsheet that pulls the data from every 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, n6). When I try to copy the pattern to the next 4 cells in the row, anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I correct this? |
Hi
this is done automatically by the thirdparameter of this formula: (COLUMN()-1)*4 So $A$1 is correct. The third parameter shifts the cell offset -- Regards Frank Kabel Frankfurt, Germany Tracey wrote: Thank you. The OFFSET command identifies the correct cell. However, when I copy to the right, the pattern doesn't repeat. It always remains anchored to the first cell (eg. A1), so all subsequent results repeat the same value. I tried removing the $ before the column, but it still doesn't work. I need the copied formula to move the anchor point over by 4 columns as the new reference. How do I do that? "Frank Kabel" wrote: Hi in the first cell (lets assume A1) enter =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4) and copy to the right -- Regards Frank Kabel Frankfurt, Germany "Tracey" schrieb im Newsbeitrag ... I am trying to copy formulas in a spreadsheet that pulls the data from every 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, n6). When I try to copy the pattern to the next 4 cells in the row, anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I correct this? |
Thanks again. However, I'm still not getting there. Here is exactly what I am
typing: =OFFSET('Plan Summary 03 - Present'!$A$6,0,COLUMN($A$6)-1)*4 What have I misinterpreted? "Frank Kabel" wrote: Hi this is done automatically by the thirdparameter of this formula: (COLUMN()-1)*4 So $A$1 is correct. The third parameter shifts the cell offset -- Regards Frank Kabel Frankfurt, Germany Tracey wrote: Thank you. The OFFSET command identifies the correct cell. However, when I copy to the right, the pattern doesn't repeat. It always remains anchored to the first cell (eg. A1), so all subsequent results repeat the same value. I tried removing the $ before the column, but it still doesn't work. I need the copied formula to move the anchor point over by 4 columns as the new reference. How do I do that? "Frank Kabel" wrote: Hi in the first cell (lets assume A1) enter =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4) and copy to the right -- Regards Frank Kabel Frankfurt, Germany "Tracey" schrieb im Newsbeitrag ... I am trying to copy formulas in a spreadsheet that pulls the data from every 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, n6). When I try to copy the pattern to the next 4 cells in the row, anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I correct this? |
Hi
use: =OFFSET('Plan Summary 03 - Present'!$A$6,0,COLUMN(A:A)-1)*4) -- Regards Frank Kabel Frankfurt, Germany Tracey wrote: Thanks again. However, I'm still not getting there. Here is exactly what I am typing: =OFFSET('Plan Summary 03 - Present'!$A$6,0,COLUMN($A$6)-1)*4 What have I misinterpreted? "Frank Kabel" wrote: Hi this is done automatically by the thirdparameter of this formula: (COLUMN()-1)*4 So $A$1 is correct. The third parameter shifts the cell offset -- Regards Frank Kabel Frankfurt, Germany Tracey wrote: Thank you. The OFFSET command identifies the correct cell. However, when I copy to the right, the pattern doesn't repeat. It always remains anchored to the first cell (eg. A1), so all subsequent results repeat the same value. I tried removing the $ before the column, but it still doesn't work. I need the copied formula to move the anchor point over by 4 columns as the new reference. How do I do that? "Frank Kabel" wrote: Hi in the first cell (lets assume A1) enter =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4) and copy to the right -- Regards Frank Kabel Frankfurt, Germany "Tracey" schrieb im Newsbeitrag ... I am trying to copy formulas in a spreadsheet that pulls the data from every 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, n6). When I try to copy the pattern to the next 4 cells in the row, anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I correct this? |
Got it! Thank you so much!
"Frank Kabel" wrote: Hi use: =OFFSET('Plan Summary 03 - Present'!$A$6,0,COLUMN(A:A)-1)*4) -- Regards Frank Kabel Frankfurt, Germany Tracey wrote: Thanks again. However, I'm still not getting there. Here is exactly what I am typing: =OFFSET('Plan Summary 03 - Present'!$A$6,0,COLUMN($A$6)-1)*4 What have I misinterpreted? "Frank Kabel" wrote: Hi this is done automatically by the thirdparameter of this formula: (COLUMN()-1)*4 So $A$1 is correct. The third parameter shifts the cell offset -- Regards Frank Kabel Frankfurt, Germany Tracey wrote: Thank you. The OFFSET command identifies the correct cell. However, when I copy to the right, the pattern doesn't repeat. It always remains anchored to the first cell (eg. A1), so all subsequent results repeat the same value. I tried removing the $ before the column, but it still doesn't work. I need the copied formula to move the anchor point over by 4 columns as the new reference. How do I do that? "Frank Kabel" wrote: Hi in the first cell (lets assume A1) enter =OFFSET('other_sheet'!$B$6,0,(COLUMN()-1)*4) and copy to the right -- Regards Frank Kabel Frankfurt, Germany "Tracey" schrieb im Newsbeitrag ... I am trying to copy formulas in a spreadsheet that pulls the data from every 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, n6). When I try to copy the pattern to the next 4 cells in the row, anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I correct this? |
| All times are GMT +1. The time now is 02:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com