+ Reply to Thread
Results 1 to 16 of 16

pulling multiple shipment dates

  1. 07-02-2015, 11:20 AM #1
    webber15
    webber15 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    michigan US
    MS-Off Ver
    2010
    Posts
    34

    pulling multiple shipment dates

    Center Bearing July Example.xlsm
    I'm looking for a formula or a code to be able to pull more than the nearest date. In the sheet i have coulmns with days of the week with date that has the quantity and date shipment date of the PN (part number)
    So from the start i run a report and place it in the a serperate sheet with the date. From there the date coulmn pulls in the data using vlookup from the corrresponding date sheet.
    I have manually input the top three part number but i would like to be able to drag down.
    Thank you in advance for your help.
    Reply With QuoteRegister To Reply
  2. 07-02-2015, 11:55 AM #2
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: pulling multiple shipment dates

    Hi,
    Try this formula in D3 and copy down
    This formula would extract five values of every part name
    =INDEX('7-01'!E:E,MATCH(INDIRECT("A"&3+QUOTIENT(ROW(A1)-1,5)*5),'7-01'!A:A,0)+MOD(ROW(A1)-1,5))

    Also I am working with the sheet name part so that you don't have to change the sheet name according to date each time when you copy across. But first tell if this would fine.
    Regards
    Sourabh
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh
    Reply With QuoteRegister To Reply
  3. 07-02-2015, 12:04 PM #3
    webber15
    webber15 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    michigan US
    MS-Off Ver
    2010
    Posts
    34

    Re: pulling multiple shipment dates

    it is pulling in numbers but they are not the correct number, also how would i match the quantity to the date?
    Reply With QuoteRegister To Reply
  4. 07-02-2015, 12:17 PM #4
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: pulling multiple shipment dates

    Wrong Values?? Are you sure?? They are same as your example....check the Columns D and E in the sheet attached.
    Sorry, If this is not what you are trying to do.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 07-02-2015, 01:24 PM #5
    webber15
    webber15 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    michigan US
    MS-Off Ver
    2010
    Posts
    34

    Re: pulling multiple shipment dates

    Okay so it is working but when there is only three order it continues to fill the last blank with the next work order. also what would i change to make it work with other columns?
    Thanks again
    Reply With QuoteRegister To Reply
  6. 07-02-2015, 01:58 PM #6
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: pulling multiple shipment dates

    Okay,
    I thought there are five for each item.
    So use this in D3
    Please Login or Register  to view this content.
    and this in E3
    Please Login or Register  to view this content.
    They are very very long!!!Lol!!

    Now copy D3:E3 and paste across to F3:W3 or till the right end.
    Now all formulas are pasted in first row.
    Now copy the first row i.e. D3:W3 and copy down till end.

    Hope this would work!!
    Reply With QuoteRegister To Reply
  7. 07-02-2015, 02:31 PM #7
    webber15
    webber15 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    michigan US
    MS-Off Ver
    2010
    Posts
    34

    Re: pulling multiple shipment dates

    Center Bearing July Example 2.xlsm
    I had to significatly cut things out because i was not able to upload it because it is too big but this is the problem that i run into.
    Reply With QuoteRegister To Reply
  8. 07-03-2015, 12:29 PM #8
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: pulling multiple shipment dates

    Ok my mistake,
    The first formula should be-
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 07-06-2015, 09:34 AM #9
    webber15
    webber15 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    michigan US
    MS-Off Ver
    2010
    Posts
    34

    Re: pulling multiple shipment dates

    Yes!! this is it thank you so much. now i only havev2 questions. one is how does the formula change from column to column and what about the date column where you able to fond a formula for that column.
    Reply With QuoteRegister To Reply
  10. 07-06-2015, 09:49 AM #10
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: pulling multiple shipment dates

    Okay, now for date, the first formula is perfect, and change the second formula to (for Cell E2):-
    Please Login or Register  to view this content.
    Now copy D1 and paste to F1, J1, and so on...
    Then copy E1 and paste to G1, K1, and so on...

    Hope that helps!!
    Reply With QuoteRegister To Reply
  11. 07-06-2015, 10:13 AM #11
    webber15
    webber15 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    michigan US
    MS-Off Ver
    2010
    Posts
    34

    Re: pulling multiple shipment dates

    if i simply copy the formula will it not pull the last day ?
    Reply With QuoteRegister To Reply
  12. 07-07-2015, 01:26 PM #12
    webber15
    webber15 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    michigan US
    MS-Off Ver
    2010
    Posts
    34

    Re: pulling multiple shipment dates

    Question, in this formula where do you declare where you will pull the date from? each day i put the data into a different sheet and it will not pull correctly because it will pull from the orignal date
    Reply With QuoteRegister To Reply
  13. 07-07-2015, 01:30 PM #13
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: pulling multiple shipment dates

    The formula extracts the date form the cell at the top which is D1 for example...
    D1 contains>>Wednesday 7/1/2015
    So date is extracted from this.....
    Reply With QuoteRegister To Reply
  14. 07-07-2015, 01:36 PM #14
    webber15
    webber15 is offline
    Registered User
    Join Date
    06-23-2015
    Location
    michigan US
    MS-Off Ver
    2010
    Posts
    34

    Re: pulling multiple shipment dates

    So because i put the different dates into different sheet what do i need to change so that when i add the new date the formula will pull from the current data sheet?
    Reply With QuoteRegister To Reply
  15. 07-07-2015, 01:36 PM #15
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: pulling multiple shipment dates

    Sample file attached...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  16. 07-07-2015, 01:39 PM #16
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: pulling multiple shipment dates

    you need to change nothing....
    when you paste the formula in a cell, the formula looks the topmost cell, and extracts the date...then the extracted date is used to look the sheet that has the name of that particular date....
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pulling specific dates out of a list of dates
    By bohodgman in forum Excel General
    Replies: 3
    Last Post: 08-07-2013, 01:09 AM
  2. Pulling Latest Dates from Multiple Sheets Then Getting Notification
    By Caulerpa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-02-2013, 03:12 PM
  3. VBA? Formula for pulling multiple dates for one part. Please help
    By bernice620 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-13-2012, 11:23 AM
  4. Pulling Multiple Dates from a Cell
    By clawinger in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2012, 12:06 PM
  5. Help pulling data between two dates
    By Caidek in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-10-2011, 11:25 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1