+ Reply to Thread
Results 1 to 7 of 7

Lookup Date 14 days prior and add multiple values for that date into column

  1. #1
    Registered User
    Join Date
    06-01-2013
    Location
    KC
    MS-Off Ver
    Excel 2007
    Posts
    3

    Lookup Date 14 days prior and add multiple values for that date into column

    Hi,

    I hope someone can help me with this problem because I can't even figure out where to start.

    I have a workbook with 2 sheets. A wages sheet and a commissions sheet. The commissions are withheld for 2 weeks so what I am trying to do is put together a Gross Salary + Commissions for that pay period.

    So if the pay period (for wages only) is 5/19/2013 to 6/1/13 I need to add in the commissions that were earned during the previous pay period (or date range) which was 5/5/13 to 5/18/13.

    For each day during that period (5/5/13 to 5/18/13), there are multiple commissions earned.

    I am trying to create a formula that will for example, on the wages sheet, look at the date on the 19th, find or search through the dates on the commissions sheet until it finds a date 14 days prior and "forall" occurrences of commissions earned on that date(which would be the 5th) add them together and pull them in to the Gross pay column on the wages sheet, also adding in the wages earned on the 19th.

    So gross pay would equal wages earned on the 19th+all commissions earned on the 5th.

    I hope this is making sense, because I'm confusing myself!!

    I have attached the workbook that I am utilizing in the hopes that someone will understand what I am trying to do here. Thanks!

    Pay and Barrel Commission.xlsx

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,841

    Re: Lookup Date 14 days prior and add multiple values for that date into column

    I'm not 100% following because you changed your logic somewhere along the way.. but if I understand your situation, you are trying to add the wages in cell D2 to the commissions earned from 5/5 thru 5/18, and not just the commissions earned on 5/5. If that is what you want, you could try this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If you do just really want the wages in D2 added to the commissions from 5/5 (14 days prior) then use this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Also... figured I should mention this... ALL of the cells in column A of the 'Barrel Commissions' sheet need to have corresponding dates in them.

    - Moo
    Last edited by Moo the Dog; 06-01-2013 at 10:17 PM.

  3. #3
    Registered User
    Join Date
    06-01-2013
    Location
    Columbus, ms
    MS-Off Ver
    Excel 2013
    Posts
    68

    Re: Lookup Date 14 days prior and add multiple values for that date into column

    If you do want to avoid inputting a date in every cell in column a then insert a Hidden Column B with the following formula and reference this column for date look-ups.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Just remember that you will have to fill this column down as your excel sheet grows. Or you can create this column, Copy and Paste it as Values, and then delete Column A and begin putting a date to every cell in the future.

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Lookup Date 14 days prior and add multiple values for that date into column

    not sure if this is exactly what you are looking for, but see attachment
    I added a helper column in 'Barrel Commissions', F2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag down

    then in 'Hourly Wages',F2 :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag down

    Hope this helps

    EDIT-
    forgot attachment..sorry
    Attached Files Attached Files
    Last edited by dredwolf; 06-02-2013 at 12:20 AM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  5. #5
    Registered User
    Join Date
    06-01-2013
    Location
    KC
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Lookup Date 14 days prior and add multiple values for that date into column

    That is EXACTLY what I was looking for! Thank you so much! I have been racking my brains for 2 days trying to figure out how to do this, especially without having to fill in the date for every single commission for the same day! That's PERFECT!! Thanks a Million Dredwolf!!

  6. #6
    Registered User
    Join Date
    06-01-2013
    Location
    KC
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Lookup Date 14 days prior and add multiple values for that date into column

    Thank you Moo, Spitzerpl and DredWolf!! I really appreciate the help on this, I don't think I would have figured this out on my own. Thank you!!

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Lookup Date 14 days prior and add multiple values for that date into column

    You are quite welcome, that's why we are here

    Please remember to mark the thread as solved if you are satisfied with your solution :
    To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1