More- AutoFill with Non-Seqeuntial Cell References ?

[moretrades]

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!

 

[Biff]

Hi!

Enter this formula in D10 and copy down as needed:

=OFFSET(Sheet2!F$209,,(ROW(1:1)-1)*4)

Biff

 

[moretrades]

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]

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

 

[moretrades]

o.k., thanks anyway for giving it some thought.