Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Blue Springs, MO, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Track monthly expenses: Can I do this in Excel?

    I have an Excel database in which I am trying to track my monthly expenses. I have attached a test database which is a start to what I wish to do.

    My problem is when I sort the records in the January worksheet by the date due column, the references in the monthly worksheet don't change. ie if a due date changes from when I originally set up the January worksheet and I do a sort by due date, the monthly worksheet cells which are linked to the January worksheet don't reflect the sort.

    I am a little vague in my verbal description, but if you look at the database, I think you will see what I am describing.

    Randall Davis
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can't do it the way you have tried. Sorting the date worksheet has no effect on the other worksheets, the links you created will remain in the total worksheet as you enter them. If I understand what you want, I assume that you will have a worksheet named after each month and you want to get the amount for each creditor in column A from the appropriate month. You can do this with the formula in B2 of the total sheet:
    =SUMPRODUCT((INDIRECT(B$1&"!$C$2:$C$13")=$A2)*INDI RECT(B$1&"!$G$2:$G$13"))

    This can be copied down and across the columns

    [note: You will have to change the creditor list in either the monthly sheet or the total sheet to identical names so the formula knows what rows to look in. Any month without a sheet will give the #ref! error]

    Steve
    Last edited by sdckapr; 2011-12-31 at 06:30.

  3. #3
    Pecoflyer
    Guest
    Also note that INDIRECT is a volatile function. So it will recalculate each time a calculation happens in the sheet.
    If that slows your work down set Calculations to manual and press F9 when you want to recalculate.

    More info on volatile functions

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Blue Springs, MO, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You are correct in your assumption that I will have worksheets for each month. I presently have the total sheet in a separate workbook. I put a copy of it in the test workbook to simplify uploading. The total workbook contains multiple years, which I have manually entered the amounts from an old MS Works spreadsheet which is similar to the Excel monthly sheet. I am trying to simplify by combining everything in Excel. I assume that the formula that you posted can be modified to get the data from the external workbook. Or, should I just put everything in the same workbook and each year create a new workbook?

    I think that having all of the worksheets in one workbook would become cluttered over time. The way I have it now, I can compare expenditures from past years without opening several workbooks.

    Thank you very much for your help.

    Randall Davis
    Last edited by randavis; 2011-12-31 at 11:10.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I would put it all into one workbook, you can use filtering to work with what you now do with worksheets. And instead of the formulas and having to ensure a match in creditor, you can use use a pivot table to update the summary.

    Note if you do use the indirect function, it will read the external workbooks, but they will have to be open. Indirect will not read closed workbooks.

    Steve
    You can even keep multiple years. You can use a page field in the pivot to select the year...

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Perth, WA, Australia
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    randavis,
    I would put all my Creditors in a Master List on the Annual worksheet and then put just the ones that I want on each Month's worksheet. On the month sheet these entries ar Validated from the Master List, to ensure that they are entered exactly so that the data for the month on the Annual sheet is:-
    =IF(ISNA(VLOOKUP($A2,JanData,5,FALSE)),"",VLOOKUP( $A2,JanData,5,FALSE))
    where ISNA() is required where Creditor on Annual Sheet is not in current month.
    and VLOOKUP() requires Table to be a Named Range when on another worksheet.
    The data on the Month sheet can be sorted as required, as can the Annual Sheet.
    I'm still working on multi years, expect to use a separate Worksheet for Summary with data linked from the "monthly totals".

    Maurice
    Attached Files Attached Files

Posting Permissions

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