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?

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?

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?

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?

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?