Absolute Reference (again)
 

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.

Absolute Reference (again)
 

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)

"LCC Jon-Kun" <LCC wrote in message
...
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.

Absolute Reference (again)
 

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!
================================================== ===


"LCC Jon-Kun" <LCC wrote in message
...
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.

Absolute Reference (again)
 

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...

"Bob Phillips" wrote:

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)

"LCC Jon-Kun" <LCC wrote in message
...
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.