# Solved: count values across multiple sheets

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

Not open for further replies.
1. ### roninn75Thread 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.

File size:
13.2 KB
Views:
49
2. ### KeebellahTrusted 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. ### roninn75Thread Starter

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

4. ### KeebellahTrusted 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. ### roninn75Thread 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. ### KeebellahTrusted Advisor

Joined:
Mar 27, 2008
Messages:
6,608
First Name:
Hans
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.

File size:
33.2 KB
Views:
41
7. ### roninn75Thread 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. ### KeebellahTrusted 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. ### roninn75Thread 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. ### KeebellahTrusted 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

File size:
36.6 KB
Views:
31
11. ### roninn75Thread Starter

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

12. ### KeebellahTrusted 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. ### KeebellahTrusted 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. ### roninn75Thread Starter

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

15. ### KeebellahTrusted 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

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.

over 733,556 other people just like you!