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.