Absolute Reference (again)

[LCC Jon-Kun]

First time posting here, but a question that most of you will probably have
heard before.

I'm trying to sort out a formula which involves taking data from one sheet
in the work book and using it on another sheet. Say for example, Sheet1 and
Sheet2.

The data I want in Sheet1 is all on row 17, but I want to use this on column
B in Sheet2

The formula I currently have is
B1 =+Sheet1'A17
when I try to copy this down column B, it turns into
B2 =+Sheet1'A18
B3 =+Sheet1'A19

I tried adding an absolute value on the 17, ie
B1 =+Sheet1'A$17
and copying down, but end up with
B2 =+Sheet1'A$17
B3 =+Sheet1'A$17

What I'm after is
B2 =+Sheet1'B$17
B3 =+Sheet1'C$17
etc etc etc
preferably without resorting to VBA or macros. I know it's possible to do if
you're going down the row instead of across, but I can't figure this out! ANy
help would be greatly appreciated.

 

[Bob Phillips]

Try this

=INDIRECT("'Sheet1'!"&ADDRESS(17,ROW(A2)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RagDyeR]

Enter *anywhere* and copy down:

=INDEX(Sheet1!$17:$17,ROWS($1:1))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


First time posting here, but a question that most of you will probably have
heard before.

I'm trying to sort out a formula which involves taking data from one sheet
in the work book and using it on another sheet. Say for example, Sheet1 and
Sheet2.

The data I want in Sheet1 is all on row 17, but I want to use this on column
B in Sheet2

The formula I currently have is
B1 =+Sheet1'A17
when I try to copy this down column B, it turns into
B2 =+Sheet1'A18
B3 =+Sheet1'A19

I tried adding an absolute value on the 17, ie
B1 =+Sheet1'A$17
and copying down, but end up with
B2 =+Sheet1'A$17
B3 =+Sheet1'A$17

What I'm after is
B2 =+Sheet1'B$17
B3 =+Sheet1'C$17
etc etc etc
preferably without resorting to VBA or macros. I know it's possible to do if
you're going down the row instead of across, but I can't figure this out!
ANy
help would be greatly appreciated.

 

[LCC Jon-Kun]

Thanks for having a look at this, but it doesn't appear to be working. This
seems to highlight cell A2 in Sheet2 when I look at the ROW(A2) reference,
and I'm not 100% sure why. The INDIRECT command is a new one on me, as is
ADDRESS, so I'm looking at the help files for them and trying to puzzle this
one out.

All I know is that this isn't copying the data from Sheet1 to Sheet2. Oh,
and I've been a bit of an idiot and missed out the "!" between 'Sheet1' and
the cell number in the example below. Been staring at these spreadsheets for
far too long...