Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Consolidate Data? (2000 SR-1)

    Have 12 workbooks, one per month, containing data on expenses in five columns. Date/Amount/Category/Detail/More Detail

    Also have another workbook containing the sub-totals by month for each category.

    So far have not been able to wrap my mind around how to get the sub-total from the individual workbook into the Totals workbook other than typing manually. Am encountering problems with Consolidate Data, probably because I'm doing something wrong. There must be an easy way to do this but the concept is just not coming to me. Not sure if Consolidate Data is even the correct feature for this. Help please. TIA

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidate Data? (2000 SR-1)

    Depending on how you've got the data laid out, a simple Link should suffice.

    If you haven't used Links before, enter a cell in the Totals workbook, type "+" and then click on the desired cell in the Subtotals Workbook. You will import the data automatically.

    Provided you lay out the Month workbooks so that the subtotals all end up being carried forward into the same place in a summary sheet with the same name, you should be able to get your Totals data automatically. HTH
    Gre

  3. #3
    Lounger
    Join Date
    Nov 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidate Data? (2000 SR-1)

    Wouldn't that be as much work as simply typing in the numbers manually?

    Maybe I should explain further. Let's say there are 20 categories. Each category may have multiple entries in a month. The data is sorted and sub-totaled. That is the number needed to go into the Totals summary page. It seems like there should be some way to electronically do this rather than manual entry. Even linking, as much or more work, would be broken if you removed the sub-totals for some reason or even moved the file from one folder to another later.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidate Data? (2000 SR-1)

    Since you're doing a fair amount of slicing and dicing, you could try the following:<UL><LI>Fix the links in theTotals workbook to a standard specific point in each Monthly Workbook. (Depending on your layout, this could be the first row of each Sheet - probably simplest to manage.) <LI>Do your data entry headers and data below that first row. Set up your subtotals. Roll them all up to the summary level. Then use "=A193" (or whatever) across each column to update your "link out" cells. New subtotals can simply update in the same manner. <LI>If you do move the Source files to another folder at a later date, an refresh is simple. Open up the Totals Workbook. Go into Edit|Links, choose the Source file in question and select Update Links. You can then point the links in the Totals Workbook to the new home of the Source Workbook.[/list]This way you would not have to keep switching between the Source and Totals Workbooks - which seems to be the most cumbersome part of the operation. HTH
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •