Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    San Francisco, California
    Thanked 0 Times in 0 Posts
    Hi, all. I've stumped my Excel guru and searched here and the net for a possible answer to this, but no luck (or my brain has frozen up). I've attached a much-reduced sample of what I'm working with (currently trying to do this with 60 billing files, with more to come). These files aren't static: no set number, some may only be tracked for a short time. Here's the situation:

    I need to track monthly billables for a variable number of files over a year. New files may be added; closed files may be deleted. I need the first page to be a summary of the individual year-to-date amounts for the files. Including the closed files. So, in the attached:

    The year starts out with Adams, Bush, Grant, Pierce, and Washington. Initially, just tracking those over two months was fine. Same file names, same cell locations on different sheets for each month. But then, in March, Jefferson and Reagan are added, thereby changing the row order and messing up the summary formula for the summary page (green boxes). (The columns will always be static, just the rows will change.)

    Then, I thought to create a "data" sheet for the amounts and using VLOOKUP. And VLOOKUP might work and I'm just not thinking about it right. But because the rows on the "data" that need to be summarized on the "summary" sheet change monthly and VLOOKUP needs to be sorted in descending order, I'm not sure if there's a VLOOKUP formula that would work each month. Also, since the row data is not uniform, it seems that might screw it up (e.g. in the attached, Jefferson and Reagan would have no data for Jan and Feb). So, the monthly amount for Washington is B6 for two months; then it becomes B8. Next month, it could be B[ANY NUMBER] but it will always be in Column B. Ditto with simply linking a cell on the Summary sheet to the "YTD Amount" column on the Data sheet - that may change each month as files are added/deleted. But the YTD amount will always be in Column P.

    Ideally, I'd like to just enter the monthly data one time (instead of the current two times) and not have to constantly update formulae (such as SUM, where the data cell might change in subsequent months). I can copy/paste the list of files from the current month to the Summary sheet, so that all the cases are on the Summary sheet. So it's not a problem with getting the file names there, but I would like to get a formula for each file to show the year-to-date amount for each file.

    Is this possible in Excel 2002? Or am I stuck with cutting/pasting the YTD column from the Data sheet? Oh, and a formula is preferred bcause sometimes the billable amounts change. Nothing like certainty in accounting, is there?

    Absolutely any thoughts/ideas are appreciated. Even if they are "no can do, muchacho". Thank you.
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Weert, Limburg, Netherlands
    Thanked 0 Times in 0 Posts

    If you re-arrange your data to this:

    File Month Amount
    Adams Jan $150.00
    Adams Feb $375.00
    Adams Mar $18.00
    Bush Jan $50.00
    Bush Feb $1,800.00
    Bush Mar $73.00
    Grant Jan $75.00
    Grant Feb $150.00
    Grant Mar $275.00
    Jefferson Mar $500.00
    Pierce Jan $200.00
    Pierce Feb $17.00
    Pierce Mar $185.60
    Reagan Mar $13.50
    Washington Jan $110.00
    Washington Feb $0.00
    Washington Mar $1,175.00

    then you can either use pivot tables for each month, or use formulas:
    For the January sheet to get the table:

    =SUMPRODUCT(Data!$C$2:$C$18*(Data!$A$2:$A$18=A2)*( Data!$B$2:$B$18="Jan"))

    The total for January:

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    Professional Office Developers Association

Posting Permissions

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