Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Add new functions to existing spreadsheet

    I have attached a spreadsheet you set up for me nearly three years ago, and now I need to add another function.

    Our company has begun paying bonuses based on how much wood our suppliers bring in throughout this physical year (1/1/15 - 12/31/15) so I need to show how many loads each company supplies each week and also keep a running YTD total for them. Each supplier needs to receive their own report each week, if they bring us logs. You can see the spreadsheet is set up so I can copy and paste on a weekly basis, but now I need to show the YTD figures. Hope you can help. You can do whatever you want to rearrange the columns and row of the spreadsheet to accomplish what's needed. Thanks in advance!
    Attached Files Attached Files

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Since you didn't mention version I am assuming 2003 or earlier so weeknum won't work in sumproduct but
    With a helper column in col i
    =weeknum(e6)
    and copied down you can use
    =SUMPRODUCT(($A$6:$A$29=J8)*($I$6:$I$29=$h$2)*$D$6 :$D$29)
    to get you started
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  3. The Following User Says Thank You to Supershoe For This Useful Post:

    k32rem (2015-01-15)

  4. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    I just saved the revised file you attached and opened it to see what it looks like. It looks amazing, and so simple and quick. I'm going to do some live entries and I will let you know how it works for me. Hope I can get it right! Thanks so much! I never would have come anywhere close doing this on my own. I'm just a "simple spreadsheet" kinda person.

  5. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Glad to help
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  6. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Okay, I've had time to work with the new file and it works great! There is one thing I will need to be able to do, though. If I could have another report sheet that would bring over the weekly totals from all the vendors who delivered logs that week. My boss is still a bit "old school", so I keep a hard copy notebook with his "Log Truck Loads Received" sheet, check stubs and weekly report summary. Thanks again, in advance!

  7. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    You could put the file on a temporary storage site.

    cheers, Paul

  8. #7
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Okay, I continue to need help with this project. I have attached three files -- The Excel file (created in MS Office Excel 2003, SP3) is the actual file I need to use that is not working properly for me; the "Weekly Totals Screen Shot" is the way that sheet should look; the "Weekly Totals Report - k32rem" is a shot of what actually comes up when I select a date for reporting. Is it because the report was redone in a later version of Excel by the person who set up this latest file? It looks great, excactly what I need, in the "Weekly Totals Screen Shot" file. Maybe it's time to upgrade to Office 2007 or 2010 anyway? Can anyone help with this? Thanks!
    Attached Files Attached Files

  9. #8
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Well K32rem,

    I fired up my old Dell XPS with Excell 2003 and opened the file. As you stated, the Weekly Totals Report was not displaying correctly. It was because of an incompatible RemoveDuplicates command I overlook in the conversion to 2003. That is corrected and it now displays the same as in the 2010 version.

    Sorry for the confusion.

    Maud
    Thanks SOOOOO much! I originally thought I was doing something wrong, but couldn't figure another way to run the weekly total file, so then I thought it must be because I use 2003 and you use 2010. I'm glad it was something as simple as a command. Guess I'll keep my 2003 for awhile yet. Thanks again!

  10. #9
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Update

    It's been awhile since I tried using the revised 2003 compatible file for this project. I am still having trouble with it and have just been using a "work around" until I made it a priority to see if this could be fixed. I don't know how to insert a screen shot directly into this message, so I saved the file and attached it. As you can see (hopefully!), the "weekly totals" page still isn't displaying properly. When Maudibe sent this revised file to me, it looked great. Something is happening in the translation to my computer that causes it to display customers that aren't supposed to be on the "weekly totals" page. I don't know if it's still an issue with my 2003 version of Excel, the way it's set up, or if there's another issue. I'm thinking it's probably time to upgrade to Office 2007 or 2010 anyway, even though I really prefer the way 2003 works. Any help would be appreciated. Thanks!
    Attached Files Attached Files

  11. #10
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    I've had a look at this and I think I know what's happening, but am not sure how to fix it. I'm sure Maudibe or one of the other superstars on this site will have a solution though!
    First the bits I could fix:
    1) There's a problem with the dates on the hidden "Settings" page. The second date column should be the first date plus 6, but is the first plus seven, so you have the same date in two different selections so are getting incorrect figures.
    2) This is also true in the code in the "Copy selected data" section where the second date should be selweek + 6, not + 7

    The big problem I can't fix is this:
    3) when the selweek variable picks up the date from the combobox, it is converting it to the date format for the local area if it can, so 1/8/15 becomes August 1st not Jan 8th when it runs the macro for me. If I manually overwrite the date into the correct format, the report then works properly. Here it is with the revised dates and a manual date:
    Capture.PNG

    Over to you, guys!

  12. The Following 2 Users Say Thank You to unclehewie For This Useful Post:

    k32rem (2015-05-14),Maudibe (2015-04-17)

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    I've been working on a version.
    Few more tests, then I'll post it.

    zeddy

  14. #12
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Could it be that you are selecting data that does NOT exist? On the sample data only goes to end of Jan. No March.
    Also, suggest consistency in design of where header row is for efficiency in setting print areas to ONE macro instead of 3. Also you do NOT need 5000 rows of formatted cells.,etc.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  15. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    I'm still having problems with my Excel2003. It just cannot create ANY activeX controls on a worksheet. This is a new problem. (The fixes for the December activeX fiasco don't work)
    See what I get when I try your file:

    Capture1.PNG

    Capture2.PNG

    So I had to use Form controls instead on my version.
    see attached

    zeddy
    Attached Files Attached Files

  16. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    Same thing on five of my PCs - even tried re-installation. Nada.

    zeddy
    Last edited by zeddy; 2015-04-21 at 06:25.

  17. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    I have attached a corrected version of my take posted in #22.
    I separated the Suppliers and Dates to different worksheets in my post#22 v1, but forgot to update the vba code which still referred to sheet [Settings].
    I would put one of your fantastic 'search-as-you-type' boxes in cell [E8] on the sheet [Delivery Log].
    This would allow quick searches for any Ticket.
    But I can't add an ActiveX control!

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-04-21 at 06:33.

Page 1 of 2 12 LastLast

Posting Permissions

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