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

"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

"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

"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

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

"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

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.

"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

"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

"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