Results 1 to 12 of 12
  1. #1
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Totals (Excel '07)

    I have been tasked with putting together a running total workbook. I have started on Aprils totals from one week to the next, using a simple = xxxxx from the previous weeks worksheet. Those figures will then be carried over into a monthly rep total. Then from there into a managers report.
    The workbook is going to get rather large as they want:

    "Note: Create new TAB every Week/Month keep in one workbook; You can keep going back to unsold customers and just tally as follow up calls.
    Report will print in landscape on 8x11. Manually enter totals from previous period tab to update running totals"

    My problems is that getting the information from one sheet to the next the way I am doing it is going to take me forever. I am looking for an easier way of tying the sheets together.
    I have attached the workbook with one sheet for March, the next 4 for April and the monthly Rep Report as well as the managers report. Those two (Rep and Mgr) I am not overly concerned with at this time.

    The attached workbook is in Excel 2003 although I am doing it in Excel 2007. It will then be saved as Excel 2003 because that is what everyone that uses this will have.

    Ideas?
    Attached Files Attached Files
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Totals (Excel '07)

    I wouldn't create a new worksheet for each week. Instead, I'd use a single sheet for data entry, looking more or less like the top part of your Pipeline Tracker sheet, but with an extra column to enter the date. You can then use SUMIF or SUMPRODUCT formulas to calculate the monthly and/or weekly totals, or create pivot tables.

  3. #3
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Totals (Excel '07)

    that would be nice, but unfortunetly once they make up their mind that that is what they want, then that is what they want.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  4. #4
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Totals (Excel '07)

    Ok I got that part solved. I thought why not see what happens if I do the first cell and then drag fill across and it worked. So that will suffice.
    Now up on the top you see in columns B-G he has an in cell drop down I used to remember how to do these but forgot and I need to add a blank in there. The Yes No etc. are located in N and O 42 & 43
    Attached Files Attached Files
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Totals (Excel '07)

    The dropdown lists are caused by Data | Validation with the List option.
    You don't really need a blank as extra option because the user can simply clear a cell. but if you prefer to have a blank in the list:
    - Select B4:B33.
    - Select Data | Validation.
    - Change the source range to =$O$41:$O$43
    - Click OK.
    - Select C4:G33.
    - Select Data | Validation.
    - Change the source range to =$N$41:$N$43
    - Click OK.

  6. #6
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Totals (Excel '07)

    Thanks Hans I remember that now!
    Ok the workbook was a huge hit with the users, but now I have two more questions:
    1: How can I add more rows to the sheets and have the Counif statement adjust to those added rows automatically say =COUNTIF(B4:B33,"NEW") so that B33 changes to say B50?
    2: what would be the easiest way to add entire worksheets and have the totals from the previous week carry over to the new sheets?
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Totals (Excel '07)

    1. If you insert rows somewhere inside the table, the formulas will adjust themselves automatically.
    If you insert rows below row 33, the formulas will *not* adjust themselves.
    You could the formula to =COUNTIF(B4:B34,"NEW"). If you then insert one or more rows immediately below row 33, the formulas will adjust themselves.

    2. You'll have to do this manually or write a macro to do it.

  8. #8
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Totals (Excel '07)

    Thanks Hans.
    I knew about inserting the rows above row 33, and will sugget that that is what they do. A macro to do this would be nice, but given the overall knowledge of Excel that I have about writing macros (basically none) and the even lesser knowledge of Excel that the users have I will stick to the insert rows above row 33.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  9. #9
    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

    Re: Totals (Excel '07)

    One trick that I use is make the last row blank and add a background color to it. Then add a text box on top of this row indicating to insert above the line. (My ranges will also start with the header row)

    When linked to the header and the this blank row, the range in the formulas should always expand.

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals (Excel '07)

    Nice trick, Steve! I can use this...

  11. #11
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Totals (Excel '07)

    That will work perfectly.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  12. #12
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Totals (Excel '07)

    the workbook as I have set up with your help is working beautifully. (see original post) Now however I need to take it one step further. In the Rep Report and Mgr Report there are two columns one is New and the other is FUP. the formula for new, is =COUNTIF(B4:B33,"NEW") since they now want the FUP totals, what is the best way to do that?
    I am thinking a new column with the same formula except "FUP" instead of new. Then I would be able to carry those figures over to the other sheets that I need to. Is that the best way or is there a better way to do this within the existing column and have the totals go where they need to which is the Rep Report and the Mgr Report? It is not necessary to have them show up on the other worksheets.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

Posting Permissions

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