Geek Question

"riverman" wrote in message ...
In Excel, how can I make worksheet references relative?

For example, I want to insert the following in row 3, in cells A through
Z:

Sheet2!$B$5 Sheet3!$B$5 Sheet4!$B$5 Sheet5!$B$5 etc.

I know how to make the cell number absolute (with the $-signs), but I
cannot get the 'fill' to change the sheets when I drag the function
command across the different cells in row 3: it keeps the worksheet
reference absolute, referencing Sheet2. How can I make the worksheet
reference relative, so it changes the sheet number for each successive
cell?

--riverman

I subscribe to microsoft.public.excell.worksheetfunctions and it appears to
be a very active NG (53,854 posts).

microsoft.public.excel.newuser had 9746 post

Op

Mr. Opus McDopus wrote:
"riverman" wrote in message ...

In Excel, how can I make worksheet references relative?

For example, I want to insert the following in row 3, in cells A through
Z:

Sheet2!$B$5 Sheet3!$B$5 Sheet4!$B$5 Sheet5!$B$5 etc.

I know how to make the cell number absolute (with the $-signs), but I
cannot get the 'fill' to change the sheets when I drag the function
command across the different cells in row 3: it keeps the worksheet
reference absolute, referencing Sheet2. How can I make the worksheet
reference relative, so it changes the sheet number for each successive
cell?

--riverman


I subscribe to microsoft.public.excell.worksheetfunctions and it appears to
be a very active NG (53,854 posts).

microsoft.public.excel.newuser had 9746 post

Is there possibly some kind of ObRoff you guys could stick here? I mean,
like, there are plenty of Geek newsgroups.

BTW, Happy Birthday, Myron. I'll be 60 in February, so **** you.

--
Cut "to the chase" for my email address.

"rw" wrote in message
m...

I subscribe to microsoft.public.excell.worksheetfunctions and it appears
to be a very active NG (53,854 posts).

microsoft.public.excel.newuser had 9746 post

Is there possibly some kind of ObRoff you guys could stick here? I mean,
like, there are plenty of Geek newsgroups.

No, but I can tell you where to stick you query, as it seem quite obvious
that I was trying to direct Myron to an appropriate Geek NG!

BTW, Happy Birthday, Myron. I'll be 60 in February, so **** you.

I turn 60 in 12 years and 94 dayz,so **** you too!

Op


--
Cut "to the chase" for my email address.

In article ,
says...

"rw" wrote in message
m...

I subscribe to microsoft.public.excell.worksheetfunctions and it appears
to be a very active NG (53,854 posts).

microsoft.public.excel.newuser had 9746 post

Is there possibly some kind of ObRoff you guys could stick here? I mean,
like, there are plenty of Geek newsgroups.

No, but I can tell you where to stick you query, as it seem quite obvious
that I was trying to direct Myron to an appropriate Geek NG!

BTW, Happy Birthday, Myron. I'll be 60 in February, so **** you.

I turn 60 in 12 years and 94 dayz,so **** you too!

I turn 60 in 23 years and 280 days, so nah! :-)
- Ken

"kjanik" wrote in message
. ..
In article ,
says...

"rw" wrote in message
m...

I subscribe to microsoft.public.excell.worksheetfunctions and it
appears
to be a very active NG (53,854 posts).

microsoft.public.excel.newuser had 9746 post

Is there possibly some kind of ObRoff you guys could stick here? I
mean,
like, there are plenty of Geek newsgroups.

No, but I can tell you where to stick you query, as it seem quite obvious
that I was trying to direct Myron to an appropriate Geek NG!

BTW, Happy Birthday, Myron. I'll be 60 in February, so **** you.

I turn 60 in 12 years and 94 dayz,so **** you too!

I turn 60 in 23 years and 280 days, so nah! :-)
- Ken

Tom = 60
7 years, 115 days, 13 hours, 42 min. 15 seconds ENTER
-tom

"rw" wrote in message
m...
Mr. Opus McDopus wrote:
"riverman" wrote in message ...

In Excel, how can I make worksheet references relative?

For example, I want to insert the following in row 3, in cells A through
Z:

Sheet2!$B$5 Sheet3!$B$5 Sheet4!$B$5 Sheet5!$B$5 etc.

I know how to make the cell number absolute (with the $-signs), but I
cannot get the 'fill' to change the sheets when I drag the function
command across the different cells in row 3: it keeps the worksheet
reference absolute, referencing Sheet2. How can I make the worksheet
reference relative, so it changes the sheet number for each successive
cell?

--riverman


I subscribe to microsoft.public.excell.worksheetfunctions and it appears
to be a very active NG (53,854 posts).

microsoft.public.excel.newuser had 9746 post

Is there possibly some kind of ObRoff you guys could stick here? I mean,
like, there are plenty of Geek newsgroups.

BTW, Happy Birthday, Myron. I'll be 60 in February, so **** you.


60!? Holy crap, when my grandparents were your age, they were dead already!

--riverman

oh, and ObRoff: FYT
g

riverman wrote:
In Excel, how can I make worksheet references relative?

For example, I want to insert the following in row 3, in cells A through Z:

Sheet2!$B$5 Sheet3!$B$5 Sheet4!$B$5 Sheet5!$B$5 etc.

I know how to make the cell number absolute (with the $-signs), but I cannot
get the 'fill' to change the sheets when I drag the function command across
the different cells in row 3: it keeps the worksheet reference absolute,
referencing Sheet2. How can I make the worksheet reference relative, so it
changes the sheet number for each successive cell?

--riverman

My smart person finally got back to me. I tried to send this to you
but it bounced. Here's the answer. It uses two extra rows which you
can hide.

start in row 1 as follows:

A1: =2
B1: =A1+1
C1: =B1+1
and so forth (by copying B1 to the right as far as you need)

Then, in row 2 generate the address text:

A2: ="Sheet"&A1&"!$B$5"
B2: ="Sheet"&B1&"!$B$5"
and so forth (by copying B2 to the right as far as you need)

Then, retrieve the values in row 3:

A3: =INDIRECT(A2)
B3: =INDIRECT(B2)
and so forth (by copying B3 to the right as far as you need)

IT worked for me.

"BJ Conner" wrote in message
ups.com...

riverman wrote:
In Excel, how can I make worksheet references relative?

For example, I want to insert the following in row 3, in cells A through
Z:

Sheet2!$B$5 Sheet3!$B$5 Sheet4!$B$5 Sheet5!$B$5 etc.

I know how to make the cell number absolute (with the $-signs), but I
cannot
get the 'fill' to change the sheets when I drag the function command
across
the different cells in row 3: it keeps the worksheet reference absolute,
referencing Sheet2. How can I make the worksheet reference relative, so
it
changes the sheet number for each successive cell?

--riverman

My smart person finally got back to me. I tried to send this to you
but it bounced. Here's the answer. It uses two extra rows which you
can hide.

start in row 1 as follows:

A1: =2
B1: =A1+1
C1: =B1+1
and so forth (by copying B1 to the right as far as you need)

Then, in row 2 generate the address text:

A2: ="Sheet"&A1&"!$B$5"
B2: ="Sheet"&B1&"!$B$5"
and so forth (by copying B2 to the right as far as you need)

Then, retrieve the values in row 3:

A3: =INDIRECT(A2)
B3: =INDIRECT(B2)
and so forth (by copying B3 to the right as far as you need)

IT worked for me.

Hmm, sounds like a clever solution. Force the cell go change the sheet
reference without knowing its a sheet reference, then recall the exact label
in another cell. However, it won't work for me...I get a REF! error. I can
get around that by inserting =INDIRECT($A$2), but then I'm back where I
started.

I'll keep playing with it, but this 'INDIRECT' command looks interesting.
Thanks for pursuing with your smart person.

--riverman

"BJ Conner" wrote in message
ups.com...

riverman wrote:
In Excel, how can I make worksheet references relative?

For example, I want to insert the following in row 3, in cells A through
Z:

Sheet2!$B$5 Sheet3!$B$5 Sheet4!$B$5 Sheet5!$B$5 etc.

I know how to make the cell number absolute (with the $-signs), but I
cannot
get the 'fill' to change the sheets when I drag the function command
across
the different cells in row 3: it keeps the worksheet reference absolute,
referencing Sheet2. How can I make the worksheet reference relative, so
it
changes the sheet number for each successive cell?

--riverman

My smart person finally got back to me. I tried to send this to you
but it bounced. Here's the answer. It uses two extra rows which you
can hide.

start in row 1 as follows:

A1: =2
B1: =A1+1
C1: =B1+1
and so forth (by copying B1 to the right as far as you need)

Then, in row 2 generate the address text:

A2: ="Sheet"&A1&"!$B$5"
B2: ="Sheet"&B1&"!$B$5"
and so forth (by copying B2 to the right as far as you need)

Then, retrieve the values in row 3:

A3: =INDIRECT(A2)
B3: =INDIRECT(B2)
and so forth (by copying B3 to the right as far as you need)

IT worked for me.


Hey, I got it to work! (I had forgotten to type the exclamation point in the
second row). I see that the actual cell that I need to have in my program is
the INDIRECT command, and the other two get it to generate the command.

Excellent; thanks!

--riverman