Results 1 to 3 of 3

Thread: How to calculate values in different spreadsheets of Excel

  1. #1
    Join Date
    Feb 2012
    Posts
    13

    How to calculate values in different spreadsheets of Excel

    I have a workbook consisting of several sheets. each sheet depicts data for several criteria 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.

  2. #2
    Join Date
    May 2011
    Posts
    448

    Re: How to calculate values in different spreadsheets of Excel

    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.
    Code:
    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

  3. #3
    Join Date
    Jun 2011
    Posts
    635

    Re: How to calculate values in different spreadsheets of Excel

    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.

Similar Threads

  1. I want to link a large amount of spreadsheets in Excel
    By M.N.S in forum MS Office Support
    Replies: 2
    Last Post: 23-02-2012, 07:43 PM
  2. Unable to copy folder names in excel spreadsheets
    By Qamar in forum Software Development
    Replies: 6
    Last Post: 14-05-2011, 11:52 PM
  3. Replies: 3
    Last Post: 28-09-2010, 10:29 PM
  4. How do you link spreadsheets together in excel
    By SADIQ in forum Software Development
    Replies: 3
    Last Post: 31-07-2009, 08:09 AM
  5. Open excel spreadsheets in separate windows
    By Abbie in forum Windows Software
    Replies: 3
    Last Post: 25-05-2009, 05:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,654,030.54200 seconds with 17 queries