Geek Question
 

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

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?

This may provide the answer you're looking for:

http://www.fgcu.edu/support/office20...functions.html

See the section labeled "Relative, Absolute, and Mixed Referencing."

Wolfgang

Geek Question
 

"Wolfgang" wrote in message
...

"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?

This may provide the answer you're looking for:

http://www.fgcu.edu/support/office20...functions.html

See the section labeled "Relative, Absolute, and Mixed Referencing."

Wolfgang

Thanks, Wolfie, but that only shows what I already know: how to make
columns, rows and cells absolute or relative. Nothing on making Worksheets
relative.
I'll keep looking also.

--riverman

Geek Question
 

"riverman" wrote in message ...

"Wolfgang" wrote in message
...

"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?

This may provide the answer you're looking for:

http://www.fgcu.edu/support/office20...functions.html

See the section labeled "Relative, Absolute, and Mixed Referencing."

Wolfgang

Thanks, Wolfie, but that only shows what I already know: how to make
columns, rows and cells absolute or relative. Nothing on making Worksheets
relative.
I'll keep looking also.

O.k., how about this one?:

http://j-walk.com/ss/excel/tips/tip63.htm

Wolfgang

Geek Question
 

riverman wrote:

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?


If I understand what you are asking, it won't normally. You either have
clip and paste from the individual worksheets (sheet1, sheet2, etc),
build a table, use the database lookup functions, or write some macro
combination.

Wayne

Geek Question
 

"Wayne Knight" wrote in message
oups.com...

riverman wrote:

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?


If I understand what you are asking, it won't normally. You either have
clip and paste from the individual worksheets (sheet1, sheet2, etc),
build a table, use the database lookup functions, or write some macro
combination.

Wayne

I think you're right Wayne, but here's a thought:
Set up a template, then at the bottom of the excel page you'll see "part
name", right click on part name select move or copy.
Select the create copy box and hit ok. Now you have page 2 set up and 3 &
as many as you want to create.
You can also name them anyway you want.
You may know this already,
fwiw,
-tom

Geek Question
 

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

Lots of Xcel forms out there.
http://www.excelforum.com/ is one. If you search google groups for
Excel you get about a dozen. I am not trying to be smart. I have used
them to get answers and it usually happens pretty fast. You don't get
near the number of smart asses do on Roff.

This guy has some pretty good stuff on his webstie.
http://www.cpearson.com/
If you goggle "Excel Consultants or help" you get many hits. I use to
have a list of about 4 that were worth reading every week, it made for
a good Excel education.
I think for your problem you can make the sheet number be a look up in
a another column.(say row 2). I think I have done that before but I
can't get it to work right now.

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

You might try microsoft.public.excel.newuser?

Op

Geek Question
 

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

"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

You might try microsoft.public.excel.newuser?

Op

Or you could try microsoft.public.worksheetfunctions!

Op

Geek Question
 

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

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

"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

You might try microsoft.public.excel.newuser?

Op

Or you could try microsoft.public.worksheetfunctions!

Op

make that microsoft.public.excel.worksheetfunctions

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

Geek Question
 

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.

Geek Question
 

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

Geek Question
 

"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

Geek Question
 

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

Geek Question
 

"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

Geek Question
 

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.

Geek Question
 

"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

Geek Question
 

"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