count values across multiple sheets
 

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(area 1, area 2, etc). furthermore fields making up the rows for those headings(e.g apples, pears, oranges, etc)
areas might overlap in town sheets.
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.

thank you for your help...

count values across multiple sheets
 

Hi Roninn

Personally i would use the Data / Consolidate feature in Excel.

Basically it will build you a combined table of all your town sheets
and sum based on the row & column headings.

To use it, click in cell A1 in a blank sheet
Choose Consolidate from the Data Ribbon (or menu depending on your
version)
Ensure the function is set to SUM
At the bottom of the dialog box tick use labels in "Top row" and "left
column"
tick the 'create links to source data' if you want to be able to
update the sheets and have the consolidated table updated
Click in the reference box, go to your first sheet, highlight the data
you have
click Add
Go to your second sheet, highlight the data you have
click Add
repeat for all sheets
click OK

Hope this helps.

JulieD

hi Julied
thank you for your reply. however how do i refresh the data automatically when it changes in the various sheets? i tried the refresh all button but that does not seem to work.