Home |
Search |
Today's Posts |
#1
|
|||
|
|||
More- AutoFill with Non-Seqeuntial Cell References ?
Biff,
Thanks for the reply. This is what I'm trying to do: on SHEET1 I'm trying to use AutoFill (or just copy) cell references vertically down column D beginiing in cell D10 with references to cells in SHEET2 (so I'm trying to enter cell references in cells D10, D11, D12, etc. of SHEET1) the cells I'm trying to reference from SHEET2 are F209, J209, N209, etc. (same row, every 4th. column). so, in SHEET1 D10 I want SHEET2 F209 in SHEET1 D11 I want SHEET2 J209 in SHEET1 D12 I want SHEET2 N209 etc. can OFFSET work for me somehow? thanks! On Sun, 19 Jun 2005 22:39:00 -0400, you wrote: Hi! If what you're try to do is this: =A2.....=E2......=I2 Or =A2 =A6 =A10 To reference horizontally across the row: =OFFSET($A2,,(COLUMN(A:A)-1)*4) To reference vertically down the column: =OFFSET(A$2,(ROW(1:1)-1)*4,,) Biff wrote in message ... In one sheet I need to reference cells in a different sheet in the same workbook. It's a long sheet of projections, so it would save me lots of time if I could use AutoFill to add references to the cells on the other sheet. The cells I want to reference are in the same row, but they're not in adjacent columns- specifically, they're located in every 5th. column (i.e. A2, E2, I2, etc.) If I start with a reference to A2 and use AutoFill, I get B2, C2, D2, etc. Is there any way to specify the realtivity of AutoFill to use every xth. row or xth. column? |
#2
|
|||
|
|||
Hi!
Enter this formula in D10 and copy down as needed: =OFFSET(Sheet2!F$209,,(ROW(1:1)-1)*4) Biff wrote in message ... Biff, Thanks for the reply. This is what I'm trying to do: on SHEET1 I'm trying to use AutoFill (or just copy) cell references vertically down column D beginiing in cell D10 with references to cells in SHEET2 (so I'm trying to enter cell references in cells D10, D11, D12, etc. of SHEET1) the cells I'm trying to reference from SHEET2 are F209, J209, N209, etc. (same row, every 4th. column). so, in SHEET1 D10 I want SHEET2 F209 in SHEET1 D11 I want SHEET2 J209 in SHEET1 D12 I want SHEET2 N209 etc. can OFFSET work for me somehow? thanks! On Sun, 19 Jun 2005 22:39:00 -0400, you wrote: Hi! If what you're try to do is this: =A2.....=E2......=I2 Or =A2 =A6 =A10 To reference horizontally across the row: =OFFSET($A2,,(COLUMN(A:A)-1)*4) To reference vertically down the column: =OFFSET(A$2,(ROW(1:1)-1)*4,,) Biff wrote in message ... In one sheet I need to reference cells in a different sheet in the same workbook. It's a long sheet of projections, so it would save me lots of time if I could use AutoFill to add references to the cells on the other sheet. The cells I want to reference are in the same row, but they're not in adjacent columns- specifically, they're located in every 5th. column (i.e. A2, E2, I2, etc.) If I start with a reference to A2 and use AutoFill, I get B2, C2, D2, etc. Is there any way to specify the realtivity of AutoFill to use every xth. row or xth. column? |
#3
|
|||
|
|||
Biff, that's a beautiful thing- worked great!
now, can you help me with the reverse scenario: on SHEET2 I'm trying to quickly copy cell references horizontally across row 42, every 4th. column, beginning in cell C42, with references to cells in SHEET1 (so I'm trying to copy cell references into cells C42, G42, K42, etc. of SHEET2) the cells I'm trying to reference from SHEET1 are L69, L70, L71, etc. (vertically down a single column). so, in SHEET2 C42 I want SHEET1 L69 in SHEET2 G42 I want SHEET1 L70 in SHEET2 K42 I want SHEET1 L71 etc. can OFFSET help here too? thanks again for the help. btw, someday when I have time I'll sit down and figure out the syntax myself! "Biff" wrote: Hi! Enter this formula in D10 and copy down as needed: =OFFSET(Sheet2!F$209,,(ROW(1:1)-1)*4) Biff wrote in message ... Biff, Thanks for the reply. This is what I'm trying to do: on SHEET1 I'm trying to use AutoFill (or just copy) cell references vertically down column D beginiing in cell D10 with references to cells in SHEET2 (so I'm trying to enter cell references in cells D10, D11, D12, etc. of SHEET1) the cells I'm trying to reference from SHEET2 are F209, J209, N209, etc. (same row, every 4th. column). so, in SHEET1 D10 I want SHEET2 F209 in SHEET1 D11 I want SHEET2 J209 in SHEET1 D12 I want SHEET2 N209 etc. can OFFSET work for me somehow? thanks! On Sun, 19 Jun 2005 22:39:00 -0400, you wrote: Hi! If what you're try to do is this: =A2.....=E2......=I2 Or =A2 =A6 =A10 To reference horizontally across the row: =OFFSET($A2,,(COLUMN(A:A)-1)*4) To reference vertically down the column: =OFFSET(A$2,(ROW(1:1)-1)*4,,) Biff wrote in message ... In one sheet I need to reference cells in a different sheet in the same workbook. It's a long sheet of projections, so it would save me lots of time if I could use AutoFill to add references to the cells on the other sheet. The cells I want to reference are in the same row, but they're not in adjacent columns- specifically, they're located in every 5th. column (i.e. A2, E2, I2, etc.) If I start with a reference to A2 and use AutoFill, I get B2, C2, D2, etc. Is there any way to specify the realtivity of AutoFill to use every xth. row or xth. column? |
#4
|
|||
|
|||
Hi!
in SHEET2 C42 I want SHEET1 L69 in SHEET2 G42 I want SHEET1 L70 in SHEET2 K42 I want SHEET1 L71 etc. can OFFSET help here too? Unfortunately, I don't think so. The problem is the range of cells you want to "plug" into are not contiguous and the space between them is not constant throughout the range. You may think it is but it isn't. I can't come up with anything that will increment by 1. In any event, this is not something that could just be drag copied. If I (or anyone else for that matter) could come up with a formula you would have to select the cells individually as a range then enter the formula using CTRL ENTER. That is, selecting C42 then while holding down the CTRL key selecting G42 then K42 then etc, etc. Considering that, it probably will not be much easier than selecting each individual cell and entering =Sheet1!L69 or whatever. Biff wrote in message ... Biff, that's a beautiful thing- worked great! now, can you help me with the reverse scenario: on SHEET2 I'm trying to quickly copy cell references horizontally across row 42, every 4th. column, beginning in cell C42, with references to cells in SHEET1 (so I'm trying to copy cell references into cells C42, G42, K42, etc. of SHEET2) the cells I'm trying to reference from SHEET1 are L69, L70, L71, etc. (vertically down a single column). so, in SHEET2 C42 I want SHEET1 L69 in SHEET2 G42 I want SHEET1 L70 in SHEET2 K42 I want SHEET1 L71 etc. can OFFSET help here too? thanks again for the help. btw, someday when I have time I'll sit down and figure out the syntax myself! "Biff" wrote: Hi! Enter this formula in D10 and copy down as needed: =OFFSET(Sheet2!F$209,,(ROW(1:1)-1)*4) Biff wrote in message ... Biff, Thanks for the reply. This is what I'm trying to do: on SHEET1 I'm trying to use AutoFill (or just copy) cell references vertically down column D beginiing in cell D10 with references to cells in SHEET2 (so I'm trying to enter cell references in cells D10, D11, D12, etc. of SHEET1) the cells I'm trying to reference from SHEET2 are F209, J209, N209, etc. (same row, every 4th. column). so, in SHEET1 D10 I want SHEET2 F209 in SHEET1 D11 I want SHEET2 J209 in SHEET1 D12 I want SHEET2 N209 etc. can OFFSET work for me somehow? thanks! On Sun, 19 Jun 2005 22:39:00 -0400, you wrote: Hi! If what you're try to do is this: =A2.....=E2......=I2 Or =A2 =A6 =A10 To reference horizontally across the row: =OFFSET($A2,,(COLUMN(A:A)-1)*4) To reference vertically down the column: =OFFSET(A$2,(ROW(1:1)-1)*4,,) Biff wrote in message ... In one sheet I need to reference cells in a different sheet in the same workbook. It's a long sheet of projections, so it would save me lots of time if I could use AutoFill to add references to the cells on the other sheet. The cells I want to reference are in the same row, but they're not in adjacent columns- specifically, they're located in every 5th. column (i.e. A2, E2, I2, etc.) If I start with a reference to A2 and use AutoFill, I get B2, C2, D2, etc. Is there any way to specify the realtivity of AutoFill to use every xth. row or xth. column? |
#5
|
|||
|
|||
o.k., thanks anyway for giving it some thought.
"Biff" wrote: Hi! in SHEET2 C42 I want SHEET1 L69 in SHEET2 G42 I want SHEET1 L70 in SHEET2 K42 I want SHEET1 L71 etc. can OFFSET help here too? Unfortunately, I don't think so. The problem is the range of cells you want to "plug" into are not contiguous and the space between them is not constant throughout the range. You may think it is but it isn't. I can't come up with anything that will increment by 1. In any event, this is not something that could just be drag copied. If I (or anyone else for that matter) could come up with a formula you would have to select the cells individually as a range then enter the formula using CTRL ENTER. That is, selecting C42 then while holding down the CTRL key selecting G42 then K42 then etc, etc. Considering that, it probably will not be much easier than selecting each individual cell and entering =Sheet1!L69 or whatever. Biff wrote in message ... Biff, that's a beautiful thing- worked great! now, can you help me with the reverse scenario: on SHEET2 I'm trying to quickly copy cell references horizontally across row 42, every 4th. column, beginning in cell C42, with references to cells in SHEET1 (so I'm trying to copy cell references into cells C42, G42, K42, etc. of SHEET2) the cells I'm trying to reference from SHEET1 are L69, L70, L71, etc. (vertically down a single column). so, in SHEET2 C42 I want SHEET1 L69 in SHEET2 G42 I want SHEET1 L70 in SHEET2 K42 I want SHEET1 L71 etc. can OFFSET help here too? thanks again for the help. btw, someday when I have time I'll sit down and figure out the syntax myself! "Biff" wrote: Hi! Enter this formula in D10 and copy down as needed: =OFFSET(Sheet2!F$209,,(ROW(1:1)-1)*4) Biff wrote in message ... Biff, Thanks for the reply. This is what I'm trying to do: on SHEET1 I'm trying to use AutoFill (or just copy) cell references vertically down column D beginiing in cell D10 with references to cells in SHEET2 (so I'm trying to enter cell references in cells D10, D11, D12, etc. of SHEET1) the cells I'm trying to reference from SHEET2 are F209, J209, N209, etc. (same row, every 4th. column). so, in SHEET1 D10 I want SHEET2 F209 in SHEET1 D11 I want SHEET2 J209 in SHEET1 D12 I want SHEET2 N209 etc. can OFFSET work for me somehow? thanks! On Sun, 19 Jun 2005 22:39:00 -0400, you wrote: Hi! If what you're try to do is this: =A2.....=E2......=I2 Or =A2 =A6 =A10 To reference horizontally across the row: =OFFSET($A2,,(COLUMN(A:A)-1)*4) To reference vertically down the column: =OFFSET(A$2,(ROW(1:1)-1)*4,,) Biff wrote in message ... In one sheet I need to reference cells in a different sheet in the same workbook. It's a long sheet of projections, so it would save me lots of time if I could use AutoFill to add references to the cells on the other sheet. The cells I want to reference are in the same row, but they're not in adjacent columns- specifically, they're located in every 5th. column (i.e. A2, E2, I2, etc.) If I start with a reference to A2 and use AutoFill, I get B2, C2, D2, etc. Is there any way to specify the realtivity of AutoFill to use every xth. row or xth. column? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Syntax for inferred cell references | Excel Worksheet Functions | |||
AutoFIll -by dragging the bottom-right corner of the cell | Excel Discussion (Misc queries) | |||
Cell References in Functiona | Excel Worksheet Functions | |||
Excel: how to formulate conditional cell references | Excel Worksheet Functions |