Ok guys - a challenge - summing from unknown worksheets
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Ok guys - a challenge - summing from unknown worksheets

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a workbook that I've created with 4 main worksheets. The first is a "logon" worksheet - the user selects a "path" to follow by clicking on one of the command buttons. (The purpose of the workbook is to dynamically create voucher sheets for expense reimbursement by project. Each project has its own workbook - each workbook has multiple worksheets.)

    So far so good. BUT (user requirement) the user renames each worksheet. Of course, there is no main list of possible names - the user could name a worksheet 'INeedAStiffDrink' if desired. The user can NOT alter the layout of the worksheet however so all the sums for each sheet are always in G39, G40, and G41.

    THEN I have a worksheet called Summary. And, you guessed it, this sheet needs to summarize all the data found in cells G39, G40, and G41 for all worksheets found within the workbook.

    How do I do this since I don't know beforehand the names of the worksheets nor the number of worksheets in the workbook???

    And yes, I know this would be easier in Access but that's not an option.

    Thanks in advance. After reading through the forum listing I figure you guys will definitely have a way to do this!

    Minthe




  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You can try to count to number of sheets on your workbook first:

    i = Application.Sheets.Count

    The above code will identify the number of worksheets on your workbook. Then based on that, you can go through each sheet to find the data you wanted:

    For i = Application.Sheets.Count To 1 Step -1
    If Application.Sheets(i).Name <> "Logon" And Application.Sheets(i).Name Then
    Value = Application.Sheets(i).range("G40").value
    End If
    Next i

    HTH

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Minthe:
    Look at DATA|CONSOLIDATE feature -- it is intended to provide the consolidation summary in your SUMMARY sheet (data is pulled from the appropriate worksheets in the Workbook).
    So, if I as a user change the name of one or more worksheets, you don't have to be concerned by whether the user changed the name of the worksheet or not.

    HTH
    Please post back if it works for you ... otherwise explain a little further and let us take it from there!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This where I am so far - I found another post with code that places the names of each of the worksheets in column A. I thought then I could try to use that as a reference to tell Excel the name of the worksheet to reference. (getting in way over my head).

    The Data/consolidate feature might be an answer - but I can't figure out how to tell it to consolidate from all worksheets in the book, not just the current worksheet? Esp since I won't know the names of the worksheets in the workbook ahead of time?

    (I am I'm explaining this well enough!)

    [ This Message was edited by: Minthe on 2002-03-25 21:13 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    You could create 2 dummy worksheets and name them Start1_02 and End1_02; these names hopefully are unusual enough that no one will want them.

    Set these 2 sheets before and after possible active sheets.

    Hide the 2 Sheets.

    Formula =sum(Start1_02:End1_02!G39)

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 21:08, Minthe wrote:
    This where I am so far - I found another post with code that places the names of each of the worksheets in column A. I thought then I could try to use that as a reference to tell Excel the name of the worksheet to reference. (getting in way over my head).

    The Data/consolidate feature might be an answer - but I can't figure out how to tell it to consolidate from all worksheets in the book, not just the current worksheet? Esp since I won't know the names of the worksheets in the workbook ahead of time?

    (I am I'm explaining this well enough!)

    [ This Message was edited by: Minthe on 2002-03-25 21:13 ]
    Hi Minthe:
    As I understand it, you have a workbook with a number of worksheets that have been templated with input on certain cells that facilitate totalling on some cell like G40. You will setup your consolidation on the summary sheet before hand referring to the worksheets by their generic names viz sheet1, sheet2, ...
    What somebody does after that to the names of the worksheets is immaterial -- you ought to look at HELP on consolidation, or some examples of it to get a good feel for it.

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Minthe

    If you are using VBA then you should always use a sheets CodeName (it's the one nOT in brackets in the Project Explorer) this cannot be changed by a user.

    If you simly have a number of Worksheet functions in your "Summary" sheet you only need to ensure it is always the last sheet. The use:

    =SUM(Sheet1:Summary!G4) This way any sheets added between these 2 sheets will automatically be include.

    Here is a VBA Function

    Function SumAllSheets(rSumRange As Range)
    Dim sSheet As Worksheet
    Dim MyVal
    Application.Volatile
    For Each sSheet In ActiveWorkbook.Worksheets
    If sSheet.CodeName <> "Sheet6" Then
    MyVal = WorksheetFunction.Sum _
    (sSheet.Range(rSumRange.Address), MyVal)
    End If
    Next
    SumAllSheets = MyVal
    End Function


  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    THANK YOU EVERYONE!

    I truly appreciate everyone's help and input. I was at my wit's end. The suggestion that seemed to be easiest and work the best for me was from the two Dave's.

    You guys single-handedly(double-handedly?)ended HOURS of frustration for me. As you can probably tell, I'm definitely NOT an Excel expert! You guys are great!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com