Summary page -advancing name of sheet

I have a workbook with 52 sheets, the names of which alternate in the following pattern:  BioPP15,  AAPP15,  BioPP16,  AAPP16,  BioPP17, AAPP17, etc.

I have a summary page which I'm using for certain key #'s from each sheet.
Example of formula for sheet BioPP15 -   =BioPP15!$21

Question - how do I need to edit this formula so that when I drag it down it will read
=BioPP16!$21
=BioPP17!$21
=BioPP18!$21  etc.

Thanks in advance
-- 
Chi dara fine al gran dolore? L'ore.
0
carrera (24)
7/7/2004 8:32:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
749 Views

Similar Articles

[PageSpeed] 9

carrera

To start with, =BioPP15!$21 is not a valid cell address.

Perhaps you meant =BioPP15!A$21

To increment sheet numbers use the following

=INDIRECT("BioPP" & (ROW()) & "!A$1")

Drag/copy down and it will increment.

Your start row will have to be adjusted.

Gord Dibben Excel MVP



On Wed, 7 Jul 2004 13:32:01 -0700, "carrera"
<carrera@discussions.microsoft.com> wrote:

>I have a workbook with 52 sheets, the names of which alternate in the following pattern:  BioPP15,  AAPP15,  BioPP16,  AAPP16,  BioPP17, AAPP17, etc.
>
>I have a summary page which I'm using for certain key #'s from each sheet.
>Example of formula for sheet BioPP15 -   =BioPP15!$21
>
>Question - how do I need to edit this formula so that when I drag it down it will read
>=BioPP16!$21
>=BioPP17!$21
>=BioPP18!$21  etc.
>
>Thanks in advance

0
Gord
7/7/2004 11:17:48 PM
Good Morning Gord
You're right, I inadvertantly left off the column designation in my question. Good catch.  It was in my formula though.

I'll try this solution as soon as I can.
Thanks for the assist.
-- 
Chi dara fine al gran dolore? L'ore.


"Gord Dibben" wrote:

> carrera
> 
> To start with, =BioPP15!$21 is not a valid cell address.
> 
> Perhaps you meant =BioPP15!A$21
> 
> To increment sheet numbers use the following
> 
> =INDIRECT("BioPP" & (ROW()) & "!A$1")
> 
> Drag/copy down and it will increment.
> 
> Your start row will have to be adjusted.
> 
> Gord Dibben Excel MVP
> 
> 
> 
> On Wed, 7 Jul 2004 13:32:01 -0700, "carrera"
> <carrera@discussions.microsoft.com> wrote:
> 
> >I have a workbook with 52 sheets, the names of which alternate in the following pattern:  BioPP15,  AAPP15,  BioPP16,  AAPP16,  BioPP17, AAPP17, etc.
> >
> >I have a summary page which I'm using for certain key #'s from each sheet.
> >Example of formula for sheet BioPP15 -   =BioPP15!$21
> >
> >Question - how do I need to edit this formula so that when I drag it down it will read
> >=BioPP16!$21
> >=BioPP17!$21
> >=BioPP18!$21  etc.
> >
> >Thanks in advance
> 
> 
0
carrera (24)
7/8/2004 1:03:03 PM
Reply:

Similar Artilces:

Match Account ID from combining street names and numbers
In one sheet, have a column with a street number (A): 115 in Column B, have a street name: Northwest. In workbook 2, have an additional street number column(C), and a street name column(D), and a 5th column with an account number, column (E) I need to find the account number in workbook 2 for a combination of street number,and street name found on the given row from the first workbook. There can be many street numbers on the same street, but only the combination of the desired number and called for street should return a value. Desired combinations are always found on the same row ...

Excel Sheet Limits
Is there a limit to the number of worksheets in a single workbook if so what is it? Is it only limited to the amount of memory I have or the size of the disk drive or is there a physical limit. Michael, yes the Maximum number of sheets in a workbook is Limited by available memory, for other limits have a look in help under specifications -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by e...

The ability to set the naming convention for EFT files.
Some banks have limits on the length of the file name that can be submitted for EFT. With the new naming convention the file names are all to long. Can you devise a method for setting the namin converntion. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www....

task and My tasks page
Two Qs 1. What exactly is the difference between the my Tasks Page and the Tasks page? 2. To assign yourself to a task (not a team task), the resource goes to my tasks or tasks page and clicks the new menu and selects task correct? In this page, the resource can now assign them self to an existing task or create a new tasks on projects that they have access to. I want the user to be able to assign them self to tasks but not create tasks. Is there a way to accomplish this? JennPenn07 -- You see the My Tasks page when you click the My Tasks link in the Quick Launch me...

How do I change my business name in Money 2007?
I installed Money 2007 and initially put in a business name that I'm trying to change now. I click on Business, and I go to Account settings and I change the name there. This changes it everywhere except for on the invoices that I print. The template's placeholder for <<company name>> is still putting what I had originally put in there. Where do I change this!? Any information would be greatly appreciated. Thanks! How about in the Invoice Designer that gets installed along with M2007? -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http...

How do I save macro so it's there for all Excel sheets?
We export and print different reports each day, and I want the same macro to run for each daily report. But when I open a new report (saved in the same folder as the other reports) the macro is gone. How do I get the macro to show up on each Excel report? Put it in your personal.xls file. http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=18 ************ Anne Troy www.OfficeArticles.com "Save a macro in Excel" <Save a macro in Excel@discussions.microsoft.com> wrote in message news:FFF29352-63C8-40C5-BFB2-AF0F1AA767D5@microsoft.com... > We expor...

name ranges #2
Hi, Sheet2 has the formula: =VLOOKUP($B$3,Sheet1!$B$2:$D$15,2,FALSE) in cell c8 How can I use a range name (names-1 for example) that refers to: Sheet1 range B2:B15 in a way that i have a small arrow beside cell B3 that show all the names in the range (names-1)? Please give us more detail, this question is not clear. What does VLOOKUP have to do with the range names and what do you mean by the names in the range names-1. And what do you mean by a small arrow which shows the names? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Khalil Handal"...

some pages landscape and some as portrait
I'm creating a 20 plus page report and some of the pages I want as landscape and some as portrait. If I change the setup on one page, it does it for all of them. Is there a solution for this? Any help is appreciated. In essence it is changing the orientation to landscape when you rotate the objects. If you were creating a booklet, the rotated page would appear landscaped. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "wardt508" <wardt508@discussions.microsoft.com> wrote in message news:004E6B0F-F54C-4...

Opening a web page through VB
I am launching the browser from Menu Item that is added to Menu bar in Microsoft Project using COM ADD IN. I am trying to call the browser in two ways 1. Using shell command directly passing url to the explore.exe Here the browser gets opened properly but control is not returned back to MSP and I have to close the MSP forcibly as it's not responding Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _ hHandle As Long, ByVal dwMilliseconds As Long) As Long Private Declare Function CreateProcessA Lib "kernel32" (ByVal _ lpApplicat...

Name
Hi, I need to use Name to define 3 discount rates. This needs to be done so that when the user of the spreadsheet enters "discount 1" it gives a value of 10%, "Discount 2" gives a value of 12% and "Discount 3" gives a value of 17.5%. Using Name I have managed to get it so that they enter for example " =Discount_1 " it gives a value of 10% but I want it so that when they enter "Discount 1" they get 10%. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly f...

pasting row to another workbook prompts because same defined name exists in new book
I have a set of weekly spreadsheets, all the same except for the dat that is entered onto them. What we ned to do is cut and paste lines from last weeks workbook int this weeks one. The problem is I have set up data validation on one o the columns based on a list of names, and so now it prompts ever singl time we try to paste into the new workbook, because the same define name 'tradelist' is in the new sheet. is there any way I can stop this prompt somehow because tis driving u mad and taking up time clicking yes or not (when it makes no differenc if we click yes OR no because its e...

Creating Multiple Pie Charts from Excel Sheet
Creating Multiple Pie Charts from Excel Sheet I have an Excel sheet with the following: -1 header row with the column names. -5 individual rows of unique numerical data underneath. How can I create 5 separate pie charts each using a separate row and the header row without going through the chart wizard for each separate row? Needless to say, I have about 75 rows in the actual example. Any suggestions? Once you have set up one pie chart, you can select this chart by clicking on it (you should see small black squares in the corners and midpoints of the chart area) then you can click <cop...

Launch new browser window from an excel web page
I am publishing an excel file to the web using the Excel Publishing feature - works great (I am not a programmer). However, I have links within the spreadsheet that I'd like to open a NEW browser window when the link is selected from the published Excel browser window... Make sense? In other words, when a link is selected from the published Excel browser window, I don't want it to replace the Excel browser window, but open a new window. Is there a command I can append to the URL in the Excel field (or some other approach) so that a new browser window will open when the link is ...

TOC and Page header
Hello to all, I would like to know if I can put a title in a page header and have it used in a TOC? If yes how can I do that? Thanks to all for the help. It's greatly appreciated. Stephane "Steve" Halle No, you can't. Word creates a TOC based only on text in the main document body. If you want your title repeated in the page header, use a StyleRef field there; to omit it on the first page of a section, enable "Different first page" for the header and footer. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA ...

Create summary tab of last entry/row on all tabs in workbook
I am tracking projects in a workbook. I have a tab/worksheet for each project. For a weekly status meeting I create a spreadsheet that has the last entry/row from each tab. Is there a way to program so I do not have to copy/paste from each tab each week? I do add and delete tabs/projects from time to time. PS- not very adept at the technical details so need to respond to me in simple terms : ) You might want to submit your question to an Excel NG since I don't see anything in your question regarding MS Access. IMHO, I would move the workbooks to an Access database rather than...

Remove the opportunity name field from form
I try to remove the opportunity name field from the opporunity form. I have set the attribute properties to 'no constraint', but I still get a lock Icon before the field on the form. Any ideas what might be locking this (it will be something standard, because we never used this field) and what we can do about it? Regards, Freek The name field is the Primary Attribute of the opportunity and therefore it is locked. You can see that when you open the Opportunity in the customizations area and view the second tab "Primary Attribute". I'm not sure but I don't thi...

Multiple domain name and multiple SMTP virtual server
Presently, my Exchange 2003 server holds mailboxes of four internet domain names. I have only one default smtp virtual server. Everything works fine. I have set "Send a copy of NDR" to a local mailbox. Therefore all non-existing user's mails of ALL domain reaches to this single mailbox. Is it possible to designate a separate mailbox for each domain? I found an article in Microsoft site, explaining to create separate smtp virtual server for each domain. Should I have to create smtp virtual server only for this purpose? Is there any workaround? TIA J Justin In order to creat...

Problem with ".Visible" sheet property
When someone initially opens a workbook, the "Workbook_Open" subroutine is immediately executed. Here are the first few lines of the Workbook_Open() subroutine: Sheet15.Visible = xlVeryHidden Sheet16.Visible = xlVeryHidden Sheet17.Visible = xlVeryHidden Sheet18.Visible = xlVeryHidden This code is executed just to ensure that sheets 15 through 18 are always hidden, even if the sheets are already hidden. So far, this code has never failed or ever caused any problems at all.....until NOW! One of my users seems to have accidentally created a situation where the abov...

Chart Axis naming question
When you set up a chart in Excel, you can format the axis' size, decimal point, etc. Is there anyway that you can add text into the axis title instead of it being just the numerical value? So, for instance, if my line chart has the Y axis with a scale of 1, 2, 3, 4, 5, is there anyway I can format the access to say 1 - Expert, 2 - Advanced, etc??? i know that I can move the chart into Powerpoint and put a text box down the axis, but I was hoping there may be a way do just do that in Excel. Thanks!! Hi, Have a look at Jon's page on creating arbitrary axis scale. http://pelti...

Applying one code block to the sheet that calls it
Some of you may recall my various questions re: unhiding columns & rows. Now that I have a code that works on Worksheets("PayPeriod_01"), I'd like to have the code respond to each of several sheets. For the purposes of our 2 week pay period, there would be 26 or so pay periods. Rather than have to copy the code 25 times, and doing a search / replace to change the sheet number, is there a way of having the one code block be called by a copy of the button, but use a variable on the sheet ( 01 , 02, 03, ... 25, 26 ) to ... 1) change the sheet name ( PayPeriod_01 , PayPeriod_...

INSERT INTO Table, values derived in code from separating a name
Hi, I have a field called [Contact Name] which is the result of a combo box. On not in list the user is asked if the name they enter is too be added to the list. The name is separated into two strings "str1l" and "str2" in code form a module as the function is called up from the not in List on the Combo box. str1 is FIRST_NAME str2 is LAST_NAME The fields in tblCUSTCONTACTS are "FIRST_NAME" and "LAST_NAME" Now I wish to INSERT into the tblCUSTCONTACTS the two strings. strsql = "INSERT INTO [tblCUSTCONTACTS] " _ ...

version differences in excel 2007
I have a large workbook in excel 2003 which uses an embedded lookup macro to report certain data by date. works well. I now need to run this workbook in excel 2007, and have some problems. - I get the range label error, although all ranges are defined names. - lookup is across multiple ranges, either as a vlookup, or as and 'intersection' between defined range names. - If I try to run it in my Student/Home version (at home), the macro will not load or run. If I run it in the Enterprise version (at work), it runs though slowly. If I run it in Office Mac2008, it won'...

Printing starts in the center not top of page
Version: Older version Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I have an older version of Word running on a G4 PowerPC mirror drive Mac, with an Epson RX595 printer. The Mac OS is 10.5.8 This arrangement has worked fine until recently when any Word document now prints from the center of the page, rather than the top. All other programs print normally. <br><br>Is there an update available for Office circa 2001? Is this possibly an issue with OS 10.5 Leopard? More likely an issue with the document itself. Word has a Vertical Alignment command. I do...

physical count sheet shows wrong departments and catagories
I am using rms version 1.2. When I try to produce an physical inventory count sheet using the filter, the departments and categories are mixed up. I set the filter as inactive= no and category name= (category). Some of the filters show completely blank and other categories show products that are in totally different departments and categories. However if I look at the product properties they all have the correct departments and properties. What is going on and how do you fix this??? ...

Postcard Mail Merge
I am trying to merge 50 records onto the postcards I have created (4 to a page/landscape) When I print them, they all merge, but you get four of the exact same address per page. How do I have one address on each postcard instead of 4 of each address. I'm going crazy! I read over past questions and it is not just a glitch in print preview, it actually does print 4. I just did it! Can I change a setting somewhere to fix this? I also tried creating my own four per page design with my own merge boxes, but I can not find a way to add the <<next record>> field you need ...