count values across multiple sheets

[Roninn75]

good day
i have a workbook consisting of several sheets. each sheet depicts dat
for several critera related to that specific sheet (the row fields ar
the same across each sheet) which is further broken down into categorie
specific for that area. in the summary sheet which also share the sam
rows i should be able to select a category (from a dropdown) which wil
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 thos
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 wa
sold in a specific area across all the towns.
if i select say 'area 2' in the area dropdown, it will search across th
sheets and display the totals of each of the items sold for area 2.

thank you for your help..

 

[julied d]

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

 

[Roninn75]

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 dat
automatically when it changes in the various sheets? i tried the refres
all button but that does not seem to work