1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: count values across multiple sheets

Discussion in 'Business Applications' started by roninn75, Feb 19, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. roninn75

    roninn75 Thread Starter

    Joined:
    Feb 19, 2012
    Messages:
    38
    Hi good day
    i have a workbook consisting of several sheets. each sheet depicts data for several critera related to that specific sheet (the row fields are the same across each sheet) which is further broken down into categories specific for that area. in the summary sheet which also share the same rows i should be able to select a category (from a dropdown) which will give me a summary of those fields in each category.
    i.e
    the headings for the categories on each sheet would be 1 to 100. for sheet 1(town1) - area 1, area 2, .., area4, sheet 2(town2) - area 5, area6, area7, and so on.
    fields making up the rows for those headings are e.g apples, pears, oranges, etc
    so the sheet displays how many of each item was sold in a specific area.
    areas might overlap in town sheets as a salesperson from town1 might have sold an item in an area belonging to a different town.
    the sheets are named by town (town 1, town 2, etc)
    the summary sheet - i wish to see how many apples, pears, oranges was sold in a specific area across all the towns.
    if i select say 'area 2' in the area dropdown, it will search across the sheets and display the totals of each of the items sold for area 2.
    i have used the formula 'sumproduct' which works well but it only works if i have all 108 areas on each sheet. this is not viable as it opens the data up to errors. i wish to know is there a way if the user select an area as a heading then populate the corresponding item with the amount sold. that way i dont have to list 108 columns.
    to break down what i have done so far:
    i created a named range for a table for the relevant areas and the fields it would populate. - Table
    i created a named range with the various sheets - SheetList
    here is the formula:
    =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C2:C7"),$C2, INDIRECT("'"&SheetList&"'!"&VLOOKUP($A$2,Table,2,0))))
    i have attached a sample workbook. any help would be appreciated.
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Looks interesing, I'll give it a shot tomorrow evening, its bed-time here so I won't be doing it now.
    If somebody eles picks it up I'll see it too. No problem.
     
  3. roninn75

    roninn75 Thread Starter

    Joined:
    Feb 19, 2012
    Messages:
    38
    thank you. looking forward to it :)
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Would a macro be a problem?

    I understand that your list may become longer and longer as time goes by?
    I would suggest a cumultaive table and a pivot table to filter selections.

    Could this be an idea?
     
  5. roninn75

    roninn75 Thread Starter

    Joined:
    Feb 19, 2012
    Messages:
    38
    hi
    when you say longer do you mean the columns may expand? then yes. if you mean the rows then no. the workbook i sent was only dummy data.
    i would prefer not to use a pivot table as it doesnt auto update. i wont be the only person using the workbook. a macro could work but i would use that as a second option. if there is no formula i could use to do what is required i would settle for a macro.
    thank you keebellah for your response so far.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I just read your answer but had something else that can be editted.
    No Pivot table
    Just take a look and see if it works, and yes I use macro's and maybe ...

    Just take a look and see.

    I'll check it out when I'm home this evening.
     

    Attached Files:

  7. roninn75

    roninn75 Thread Starter

    Joined:
    Feb 19, 2012
    Messages:
    38
    thank you. it gives a runtime error though when i open it up.
    other than that it does exactly what i want :)
    i see though you have added another table with the sheet reference and column reference for that sheet. in the actual workbook i have 11 sheets, could i just append following the same parameters?
    then looking at your code:
    Code:
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then
        For xCol = 4 To wsS.Cells(1, Columns.Count).End(xlToLeft).Column Step 2
            sCol = GetColumn(xCol)
            lstRow = WorksheetFunction.Max(2, wsS.Range(sCol & Rows.Count).End(xlUp).Row)
            wsS.Range(sCol & "2:" & sCol & lstRow).ClearContents
        Next xCol
        Exit Sub
    End If
    Code:
    for xcol = 4
    - i cannot estimate how many columns there would be for each sheet. i can however say that there will never be less than 4. (i am talking about the area columns in the dummy workbook)
    if you could possibly just clarify this and also look at the runtime error it kicks out when i open the workbook. i am using office 2010.
    again thank you :)
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    In theory the macro should trap all the values you put there.
    The code should in theory not need any change.

    However, if you add columns and so you will have to save and reopen the files before you run it of run the opening macro first beodre running the calculate macro.
    Probably sounds more complicated that it is but the firts macro initializes all the sheets and areas it finds.
    If it still gives the runtime error maybe you could post the file you're working on (if the data is non-private non-sensitive).
     
  9. roninn75

    roninn75 Thread Starter

    Joined:
    Feb 19, 2012
    Messages:
    38
    thank you i understand.
    the runtime error was actually on the sample you provided. i havent copied it over to the workbook i will be using it on as yet. will only be able to do so tonight or tomorrow for the latest.
    if it does give the same error i can pm you the workbook if thats ok with you. cannot upload it into a public forum though....
    thanks
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    No problem.

    I did do some editting to allow for a dynamic expansion.
    If you add ranges after column H (no emprty columns) or add sub categories these will automatically added to the summary sheet.
    You will of course have to still include them in the appropriate sheets to be counted.
    I think I degbugged these OK.

    Your will have to copy the 2 VBA modules to your sheet as well as the VB code in the Summary sheet for it to work.

    Make a backup copy before testing.'

    See my PM
     

    Attached Files:

  11. roninn75

    roninn75 Thread Starter

    Joined:
    Feb 19, 2012
    Messages:
    38
    thank you. i have mailed you the actual... please see the error i get.
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    You actually did NOT follow the instructions.
    The RANGES sheet must keep the TOP ROW for the names the ranges will get, you have not doen so hete, the top row contains invalid names for ranges, the numbers as well as the name with a space in cloumn E, check my sample again, this is the reason you get the 1004 error.
    Back to the drawing board for you :)
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Second thing I notoced is the empty row in the TABLE range, that will not work either, the range must be the next adjacent coloum after the name
    Not OKay:
    Ward 61 SMT Monthly Report < empty column > G466:G489

    This way
    Ward 61 SMT Monthly Report G466:G489
     
  14. roninn75

    roninn75 Thread Starter

    Joined:
    Feb 19, 2012
    Messages:
    38
    Thank you. looking at it now.
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Let me know how it goes.
    Please refer to the yellow text box in my working sample.
    Top row should be range names, that's what the macro expects
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1041834

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice