Summary Page - advancing name of sheet - PART DEUX

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
-- 
Chi dara fine al gran dolore? L'ore.
0
carrera (24)
7/9/2004 9:29:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
383 Views

Similar Articles

[PageSpeed] 10

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

On Fri, 9 Jul 2004 14:29:01 -0700, "carrera"
<carrera@discussions.microsoft.com> wrote:

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

0
Gord
7/9/2004 9:42:13 PM
Hi
you may try
=INDIRECT("'BioPP"&ROW(13:13) &"'!K$8")





--
Regards
Frank Kabel
Frankfurt, Germany


carrera wrote:
> 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

0
frank.kabel (11126)
7/9/2004 9:48:47 PM
> =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
----
"carrera" <carrera@discussions.microsoft.com> wrote in message
news:0E0CABCF-04A7-44AD-8DD6-CCB017CF8495@microsoft.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.
>
> 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
> --
> Chi dara fine al gran dolore? L'ore.


0
demechanik (4694)
7/9/2004 10:08:41 PM
Reply:

Similar Artilces:

Printing Page Numbers for Report Groups
Hello, Please help me, I use this kod for doing the above, but it's not working (this from http://www.mvps.org/access/reports/rpt0013.htm) What am i missing ??? Option Explicit Dim GrpArrayPage(), GrpArrayPages() Dim GrpNameCurrent As Variant, GrpNamePrevious As Variant Dim GrpPage As Integer, GrpPages As Integer Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer) Dim i As Integer If Me.Pages = 0 Then ReDim Preserve GrpArrayPage(Me.Page + 1) ReDim Preserve GrpArrayPages(Me.Page + 1) GrpNameCurrent = Me!menahel If GrpNameCurrent = GrpNam...

RPC over HTTP Advanced Server w/ Advanced Server & Exchange 2k3
Is is possible to implement RPC without installing Advanced Server 2003? I'm still using Server 2000 but have just upgraded my exchange to 2003. Thanx Requirements to use RPC over HTTP To use RPC over HTTP, your computers must meet the following requirements. Server requirements Your computer must be running Microsoft Windows Server 2003. You must install Windows Server 2003 on the computers that are running Exchange Server 2003. RPC over HTTP requires Windows Server 2003 and Exchange Server 2003. RPC over HTTP also requires Windows Server 2003 in a Global Catalog role. Client requ...

HELP!! Repeating Text over multiple Pages
I created a template containing a title, picture, map, and text box. The text box in particular contains information pertaining to a specific location. I created a 131 individual files using that template and everything came out looking great (thanks Microsoft). I had to create 131 individuals files because we needed to have it backed up for our server at work. The problem I am having now is when I attempted to combine each of 131 files by copying and pasting them into 1 publisher file containing 131 pages. The title, picture and map all preserve their own information. HOWEVER, the in...

how do I delete a blank second page on a document I have saved
I have a document saved in word which has a blank second page that prints out every time I print the document. How do I delete tat page without deleting my saved document? See http://sbarnhill.mvps.org/WordFAQs/BlankPage.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Butter" <Butter@discussions.microsoft.com> wrote in message news:E03F3F53-78F9-4873-97F0-961A7A311FA8@microsoft.com... >I have a document saved in word which has a blank second page that prints >out > every time I print th...

Embedded 2 page Newsleter in email, keeping hyperlinks
I have a two page newsletter that I would like to embed in an email, not send as an attachment. Is there any way to keep the format, show both pages and keep the hyperlinks? Try doing a copy and paste into an email template. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "gottahavemymusic" <gottahavemymusic@discussions.microsoft.com> wrote in message news:2AB8EC6B-D035-4799-BE2F-210ACB6B5C75@microsoft.com... >I have a two page newsletter that I would like to embed in an email, not >sen...

Can I print one page landscape and the next page portrait on same.
I am trying to print the 3 pages of one worksheet and can not figure out how to make page one print out as landscape, page 2 to print out as portrait and page 3 to print out as landscape. I'd record a macro when I printed page 1 as landscape, then page 2 as portrait and then page 3 as landscape. So I could just rerun that macro when I needed it. lisajillian wrote: > > I am trying to print the 3 pages of one worksheet and can not figure out how > to make page one print out as landscape, page 2 to print out as portrait and > page 3 to print out as landscape. -- Dave Pet...

mass definitions of cell names
Ok, I have a problem. I have to name a large number of cells in different sheets. I'm hoping that there is a way in a macro, or some other way to define these in an easier way other than one by one. Here is an example: 1 2 3 4 1001 --------- --------- --------- --------- 1002 --------- --------- --------- --------- 1209 --------- --------- --------- --------- 1210 --------- --------- --------- --------- This would be the column and row headers for the cells. Below is how the...

Can one change the font or format of a sheet name?
I have a large workbook using Excel 2003. I would like to format the tabs/sheet names so that certain departmental divisions are easily highlighted/identified. Ideally, the tab names could be a different font, bold, and in a color... Right Mouse Click on the sheet name > Tab Colour> select colour you want "KMcGrail" wrote: > I have a large workbook using Excel 2003. > > I would like to format the tabs/sheet names so that certain departmental > divisions are easily highlighted/identified. > > Ideally, the tab names could be a different font, bold, ...

PAGE NUMBERS IN PUBLISHER #2
I have upgraded to Publisher 2000. I have a 50 page document which I use as our numbered job sheets. Under old version I could go into tools/options and re-set the 'start at' page number. In the new version it appears I have to go into Insert/Section and put in 'start at number. ' This is all very well, but won't allow me to insert a number higher than 1000 !! Help please! CLWG <CLWG@discussions.microsoft.com> was very recently heard to utter: > I have upgraded to Publisher 2000. I have a 50 page document which > I use as our numbered job sheets. ...

Open hyperlink
In the absence of another method to check Bluecoat, I decided to copy/paste the web page to a sheet and get the values I need from two cells - not pretty but it works. First part of the question How do I ensure the webpage has finished loading before copy? Second part How can I stop IE8 asking the Q it always asked when first run about using default settings (accelerators, etc)? -- Steve ...

Person Name smart tag Performance Problem
Dear all, I'm writing a Person Name smart tag for the persona menu in Outlook and am creating dynamic caption with the IsCaptionDynamic() function. However, there is a noticable delay when popping up the smart tag("Additional Actions") Sub-menu. At first I suppose the VerbCaptionFromID2() function is only called as many times as the number of verbs, but then I noticed that it is called about the "square of number of verbs" times. I'm wondering if this behaviour cause pop-up slow. Is there any way to reduce the calling times? Thanks in advance~ Regards, Albert H...

Search All worksheets for string & Create new Whorsheet with the Original Sheet Name and some cells.d some
I am running Excel 97 SR-1. Here is what I want to do: I have a workbook that has many worksheets- all the sheets are in the same format. I would like to be able to search through the entire workbook and copy all rows that contain my "FIND DATA" to a new worksheet. The find data is really a name. Here are the specifics - The new worksheet would have the original(found) worksheet name in cell A, and all the data from the original cells ,in cells B through J, put in cells b through J. I am working on a macro to do this, but I am not having too much luck. I have searched this group...

Page 1 is full with data...now what??
Page 1 of my spreadsheet is initially empty, except for the cell borders which mark off a 20x5 spreadsheet area. (ie, there are 20 rows and 5 columns where data will be entered). Once all the cells on Page 1 of my spreadsheet become full of data, I might need to add more data to this workbook. Once the area on Page 1 is full, is it possible to create another empty table on Page 2 so more data can be entered onto a new table that is formatted exactly like the table on Page 1?? I guess what I'm really looking for is a type of smart workbook that automatically creates a newly...

A full page of email text shrinks to one inch or less w/ new batte
I was answering emails and everyhting was fine. The batteries in my wireless mouse died. I replaced them and went back to answering emails. When I clicked the "forward" or "reply" button , a full page text in an email shrank to about one inch. Changing the font size did not correct the problem. HELP it's your zoom setting. Hold ctrl and roll the mouse wheel to increase. Depending on the mouse, you could have accidently pressed a mouse button and changed it when you changed the batteries. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.o...

Merger Two Data Sheet
Hi All: I need to merge two variables but I could not find any instructions on this issue. I have a data sheet 1 like this: Course Hours Acc100 3.0 Acc122 3.0 Mar095 3.5 Bus100 4.0 Bus101 3.0 I have data sheet 2 like: Course Instructor Acc100 Charles Acc101 David Acc122 James Mar095 Mary Mar100 Greg Mar155 Sharon Bus100 Lisa Bus101 Sue Bus105 Tom Data sheet 1 and data sheet 2 have the same variable Course. Course contains much more records in data sheet 2 and all records of Course in data sheet 1 are incl...

Automatically update pivot table when switching to sheet that the pivot table is in
What would the code be to automatically refresh a pivot table when you open the sheet that the pivot table is in? Today, I changed data in the sheet that the pivot table draws from and forgot to refresh the pivot table. Fortunately, I caught my error in time and refreshed the table before I gave it to the boss. I would like the pivot table to refresh whenever I switch to the sheet that the pivot table is in. Thanks Try using the Worksheet_Activate event to update the PivotTable like: Private Sub Worksheet_Activate() PivotTables("PivotTable1").RefreshTable End Sub hth, jay <b...

Inter sheet formula won't format properly
Hi MS Excel 2003. I am writing a formula with a reference to a cell in another sheet, and Excel is behaving oddly. Sometimes it will give me the result, ie what is in the target cell, but sometimes it gives me the formula. The formula I am writing is simply ='2'!A22 thereby trying to get cell A22 from the sheet called 2. Why is Excel not always just giving me what's in the target cell? Sometimes all I can see is ='2'!A22. It's driving me nuts. I have looked at all the formatting options with no luck; I have toggled (using Ctrl`) the formula view with the norma...

Problems with updating category names in pivot tables
I have an issue with pivot tables, previously my pivot table contained months where each month was written like "January, February" etc, but now I have changed the way the months are written to "01-january, 02-february". My problem is that even though the data with the old month names no longer exists in the pivot table, the pivot table still provides "January, February" etc. as filtering options in the month category. I've tried updating the pivot table but it doesnt help. The only way i've been able to get rid of this was by creating a new pivot t...

message configure office xp with front page
Hi. I have a problem when starting up Excel. A message window comes up and says 'please wait while office xp configures with frontpage' . Then asks for the installation CD of office XP. I don't have the installation CD, and I don't use front page. The message came up a while back and I lost the excel icon entirely from Office toolbar. I had to pin it to startmenu manually from office short cut bar. Is there any chance I can end this problem? I think it has something to do with Visio which I installed and uninstalled after about a week because I didn't need it. Wi...

Publisher 2003 won't print part of a page
I have been printing a newsletter for my garden club for 2 years; today, it prints everything on 3 pages except for a portion in the right column and under the heading on page 1. Any suggestions? Printer driver update? Are you using the same publication over and over? Might start from scratch, copy/paste from the old into the new. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "capri" <capri@discussions.microsoft.com> wrote in message news:A0549BBC-BC3F-4DBF-96AD-198CDAB91AFE@microsoft.com... >I have been pri...

Multipage
I have a form with a multipage with pages names page1, page2, etc... and I have a button on the form that makes the next page visible if information is entered onto the first page. Is there code to make page2 as the focus when the button is pushed. I have code: Dim page2 As Object Set page2 = MultiPage1.page2 If page1.visible = False And ComboBox1 <> "" Then page2.visible = True currently all it does is add the page, it doesn't change the focus from page1 I'm looking for something like this... Dim page2 As Object Set page2 = MultiPage1.page2 If page1.visible = ...

Named Range, hidden row, auto-filter & macro buttons
My named range starts w/Row 14, which is hidden. A macro button inserts a new row below 14 so users may enter a new record. There are several reasons I’m doing it this way. 1) Inserting a new row between 14 & 15 ensures my named range is never altered, 2) Row 14 has conditional formatting that is copied onto Row 15 - keeping fields yellow until data is entered, and 3) the new record is always entered directly below directly below column titles. Aside from entering records I want users to be able to easily navigate through the large list or create custom narrowed down...

Close button on Crystal Reports page
Hello Community When an ASP.Net application produces an embedded Crystal Report, if the Crystal Report is in fullscreen mode is there a way to put an Close button on the Crystal Report so that when the button is clicked the Crystal Report will close and the application screen behind it that called it can be visible again? Thanks Jeff -- JB ...

Named cells to R1C1
I have worksheets with formulas referencing cell names. Is there any way to easily convert the formulas from cell names to cell references? TIA George George Through VBA can be done easily. See the various codes at this google search result. http://snipurl.com/7qnj Gord Dibben Excel MVP On Tue, 13 Jul 2004 15:56:04 -0700, "George Wilson" <anonymous@discussions.microsoft.com> wrote: >I have worksheets with formulas referencing cell names. Is >there any way to easily convert the formulas from cell >names to cell references? >TIA >George ...

How can I know when a printer has finished a page?
Hi, I have a problem where I am printing multiple pages on preprinted paper from a tray and sometimes a different preprinted paper must be inserted into the printer in the manual feed for one or two pages then back to the tray feed, so I have the program display a dialog to prompt the user to insert the new paper. The problem is because of spooling if the user does not know to wait for the printer to pause, they can insert the paper at the wrong point. So what I realy need to know is there anyway of the printer to tell my program when it has finished printing pages then I can display the di...