Sheet names used in formulas
 

I have a question about using sheet names in a formula. I would like the
sheet name to go up by one page for each row I put the formula in...(ie)
13A reads (="S40'!$W$5)... I need to have row 14 read (='S41'!$W$5)

There are many many worksheets...260 in all that need this on them. Each
worksheet is about 300 rows long. How can I do this without having to change
the sheet name by hand???

Any help is greatly appreciated!!!

Hi!

Here's one way:

=INDIRECT("S"&ROW(A40)&"!$W$5")

Which evaluates to:

=S40!$W$5

As you drag copy down:

=S40!$W$5
=S41!$W$5
=S42!$W$5
=S43!$W$5
etc..

Biff

-----Original Message-----
I have a question about using sheet names in a formula.
I would like the
sheet name to go up by one page for each row I put the
formula in...(ie)
13A reads (="S40'!$W$5)... I need to have row 14 read
(='S41'!$W$5)

There are many many worksheets...260 in all that need
this on them. Each
worksheet is about 300 rows long. How can I do this
without having to change
the sheet name by hand???

Any help is greatly appreciated!!!
.

frustrated

In A13 enter this and drag/copy down

=INDIRECT("S" & (ROW()+27) & "!W5")


Gord Dibben Excel MVP


On Thu, 24 Feb 2005 11:51:04 -0800, frustratedwthis
wrote:

I have a question about using sheet names in a formula. I would like the
sheet name to go up by one page for each row I put the formula in...(ie)
13A reads (="S40'!$W$5)... I need to have row 14 read (='S41'!$W$5)

There are many many worksheets...260 in all that need this on them. Each
worksheet is about 300 rows long. How can I do this without having to change
the sheet name by hand???

Any help is greatly appreciated!!!

Isn't it easier to use

ROW(40:40)

instead of

ROW()+27

?


--

Regards,

Peo Sjoblom


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
frustrated

In A13 enter this and drag/copy down

=INDIRECT("S" & (ROW()+27) & "!W5")


Gord Dibben Excel MVP


On Thu, 24 Feb 2005 11:51:04 -0800, frustratedwthis
wrote:

I have a question about using sheet names in a formula. I would like the
sheet name to go up by one page for each row I put the formula in...(ie)
13A reads (="S40'!$W$5)... I need to have row 14 read (='S41'!$W$5)

There are many many worksheets...260 in all that need this on them. Each
worksheet is about 300 rows long. How can I do this without having to
change
the sheet name by hand???

Any help is greatly appreciated!!!

Yes

But I not so smart like you so use calculator first to figure out how many to
add<g

Now, if it was GAA or GF/A I wouldn't need a calculator(at least this year)

Gord

On Thu, 24 Feb 2005 16:33:41 -0700, "Peo Sjoblom" wrote:

Isn't it easier to use

ROW(40:40)

instead of

ROW()+27

?

He guys,

I used =INDIRECT("S"&$B14&"!$F$6")

Thanks so much for your help!!!

"Gord Dibben" wrote:

Yes

But I not so smart like you so use calculator first to figure out how many to
add<g

Now, if it was GAA or GF/A I wouldn't need a calculator(at least this year)

Gord

On Thu, 24 Feb 2005 16:33:41 -0700, "Peo Sjoblom" wrote:

Isn't it easier to use

ROW(40:40)

instead of

ROW()+27

?