Summary Page - advancing name of sheet - PART DEUX

[Guest]

Asked question previously regarding I have a linked summary page and was having problems dragging down the formula to reflect the various worksheets.
Sorry if I'm repeating myself, please bear w/ me. The formula given to me is not working - I'm obviously doing something wrong.

Synopsis: My file has 52 sheets, the names of which alternate in the following pattern: BioPP15, AAPP15, BioPP16, AAPP16, BioPP17, AAPP17........

My summary page is for key #'s from each sheet
Example of formula for sheet BioPP13 - =BioPP13!K8 I want to drag it down so it will reflect the same cell, K8 from sheets BioPP14, BioPP15 etc.

Gord (thanks) supplied me with the following formula
=INDIRECT("BioPP"&(ROW())&"!K$8")

When I entered in my summary sheet exactly as is I get #REF!
When I enter replacing ROW with 8, I am told there is a formula error.
Obviously when I drag down it makes no sense.

What am I doing wrong?
Thanks In Advance
Everyone have a great weekend

 

[Gord Dibben]

The ROW() returns the row number of whatever row you start the formula.

For BioPP13 you would start the formula in row 13 and drag/copy down from
there.

To start in any other row, say row 1, the formula would be

=INDIRECT("BioPP"&(ROW() + 12)&"!K$8")

Gord

 

[Frank Kabel]

Hi
you may try
=INDIRECT("'BioPP"&ROW(13:13) &"'!K$8")

 

[Max]

=INDIRECT("BioPP"&(ROW())&"!K$8")

The ... ROW() ... part in the formula is sensitive
to where you put it.

ROW() returns the number of the row it is in, viz.:
In say, B2: =ROW() returns 2,
In B3, it'll returns 3, and so on, when you copy down

Example of formula for sheet BioPP13 - =BioPP13!K8
I want to drag it down so it will reflect the same cell,
K8 from sheets BioPP14, BioPP15 etc.

In your summary sheet
-------------------------------

Maybe try a slight revision
(which can be put in any start cell, say C4?):

In C4: =INDIRECT("BioPP"&ROW(A13)&"!K8")
Copy C4 down to C6

This will return the value in K8
from sheets BioPP13, BioPP14 and BioPP15
in C4 to C6

ROW(A13) will return the row of cell A13 which is 13
and also increments in the same way as ROW()
when you copy down
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Asked question previously regarding I have a linked summary page and was

having problems dragging down the formula to reflect the various worksheets.

Sorry if I'm repeating myself, please bear w/ me. The formula given to me

is not working - I'm obviously doing something wrong.

Synopsis: My file has 52 sheets, the names of which alternate in the

following pattern: BioPP15, AAPP15, BioPP16, AAPP16, BioPP17,
AAPP17........

My summary page is for key #'s from each sheet
Example of formula for sheet BioPP13 - =BioPP13!K8 I want to drag it

down so it will reflect the same cell, K8 from sheets BioPP14, BioPP15 etc.