Sheet names used in formulas

frustratedwthis · 02-24-2005, 04:06 PM

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!!!

Biff · 02-24-2005, 05:06 PM

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

Gord Dibben · 02-24-2005, 08:06 PM

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
<[email protected]> 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!!!

Peo Sjoblom · 02-24-2005, 08:06 PM

Isn't it easier to use

ROW(40:40)

instead of

ROW()+27

?

--

Regards,

Peo Sjoblom

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> 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
> <[email protected]> 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!!!
>

Gord Dibben · 02-24-2005, 10:06 PM

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" <[email protected]> wrote:

>Isn't it easier to use
>
>ROW(40:40)
>
>instead of
>
>ROW()+27
>
>?

frustratedwthis · 02-25-2005, 10:06 AM

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" <[email protected]> wrote:
>
> >Isn't it easier to use
> >
> >ROW(40:40)
> >
> >instead of
> >
> >ROW()+27
> >
> >?
>
>

**sowetoddid** · 05-09-2005, 02:32 PM

My first sheet is "Month"

All following sheets are "1", "2", "3", etc.

On the month sheet, I have 31 rows and a column for every data set. In B1 I want to reference the value in B10 on sheet "1". I cell B2 I reference the value in B10 on sheet "2". I would like to drag across and move the formula reference to the next sheet each time.

=INDIRECT("ROW(A1)"&"!B3") did not work for me. What am I doing wrong?

Thanks.

**sowetoddid** · 05-09-2005, 02:37 PM

STRANGE...

If I type this in a cell

='ROW(A10)'!B3

It opens a window with the title "Update Values: =ROW(A10)" and allows me to select other excel files??? I'm not sure what it is doing there ~~

**sowetoddid** · 05-09-2005, 03:20 PM

I figured it out....

=INDIRECT(ROW(A1)&"!B3")

...I needed to leave the quotations out.

**TEAM** · 05-09-2005, 03:27 PM

Can someone with excel formula wisdom please explain to me how INDIRECT works in this situation?? I think it is a very useful function, but it confuses the heck out of me.

My understanding is that if the value in cell A1 is "A7" and I type "=INDIRECT(A1)" into cell B5, then cell be five will show the value that is in A7.

If I type "=INDIRECT("A7")" into cell B5, then the value in A7 will be displayed. This is good when rows are added/removed. The reference to A7 is always stuck to that specifc row.

But, now, I see the formulas above and get lost in the theory. What happened to the single quotations used in a sheet reference? Also, why must INDIRECT be used in the case?

Anyone, please add your insight!!

**sowetoddid** · 05-10-2005, 08:53 AM

TEAM,

I only manipulated the formula that was already given in this post...can't help you on that one.

**swatsp0p** · 05-10-2005, 09:19 AM

You asked "What happened to the single quotations used in a sheet reference?"

Single quote is used when referencing a different workBOOK, not sheet. So in this case, the ' is not needed.

Use of INDIRECT allows the user to create a cell reference from multiple components, stored in various cells somewhere in the workbook.

In sowetoddid's example:

=INDIRECT(ROW(A1)&"!B3")

(ROW(A1) returns the value of "1" which is concatenated with "!B3", the final result of the formula, therefore, is:

=1!B3 which is a valid reference to cell B3 in the sheet named "1"

HTH

Bruce