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.

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 Jon-(E-Mail Removed)> wrote in message
news:C98C9C64-FCCD-4288-A7AA-(E-Mail Removed)...
> 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.

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 Jon-(E-Mail Removed)> wrote in message
news:C98C9C64-FCCD-4288-A7AA-(E-Mail Removed)...
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.

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 Jon-(E-Mail Removed)> wrote in message
> news:C98C9C64-FCCD-4288-A7AA-(E-Mail Removed)...
> > 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.
>
>
>