+ Reply to Thread
Results 1 to 8 of 8

IF , VLOOK formula help

  1. #1
    Registered User
    Join Date
    08-13-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Question IF , VLOOK formula help

    Hi,
    I am trying to build a formula pull a list of task (Processes) depending on the product type, I have 40+ different type of products each has a differetn set of processes, I have attached the excel book I working on, MTime sheet include the list of Products in A3-A45, the list processes B2-L2 and the time is B3-L45, on Sheet 1 I have created a product drop list which I need when I chose the product it will fill in the processes / tasks name and the time against each
    Kindly advise and help
    Thank you,
    Ibraheem
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF , VLOOK formula help

    How do you display multiple matches?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-13-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF , VLOOK formula help

    in a list under tasks in sheet 1, so against each products there will be several tasks listed verticaly
    Thanks

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF , VLOOK formula help

    Try this method:

    In A6 of Sheet1 enter formula:

    =IF(B6<>"",1,A5+1)

    copied down as far as your column B data validation goes. This serves as a counter. You can hide the column if you wish.

    Then in C6:
    Please Login or Register  to view this content.
    Confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down same distance.

    In D6:

    Please Login or Register  to view this content.
    copied down.

    Now make a selection in B6, you should have results.

    After the last result, make another selection in column B, the corresponding results should appear, then go to bottom and make another selection in B, and so on...

    when you are clearing for new entries, make sure to only delete entries made in column B
    Last edited by NBVC; 08-13-2012 at 02:49 PM. Reason: formula typo fixed

  5. #5
    Registered User
    Join Date
    08-13-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF , VLOOK formula help

    Thanks, it worked like magic, I only made a small change in D6 formula to start from $B$3 rather than $B$15 to include all data

    In D6:

    Please Login or Register  to view this content.
    Thanks again
    Ibraheem

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF , VLOOK formula help

    Good catch... and I apologize for the error.. I fixed it in the above formulas...

    Thanks for the feedback.

  7. #7
    Registered User
    Join Date
    08-13-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: IF , VLOOK formula help, Sorting , Filtering and copying rows

    Master Production Plan 1.2 t.xlsm
    Hi NBVC,
    I have used your formulas to create the attached schedule, it worked like a charm , the issue I am facing now is the filtering and sorting of information, when I select a name from the Manpower drop list and sort the schedule by the start date it through the schedule out of whack and information goes out of order, what I am trying to do is print out a schedule for individual manpower and filtered by the start date rather than the ship date
    I am thinking of creating another book that pulls and filter the information and segregate them by months each tab on the new book for one of the names on the manpower list and flitted by the Start date not the ship date
    the size of the original schedule is more than 10,000 rows, had to reduce it to be able to attach it
    Please let me know if I missed anything
    Thank you for your help and support
    Cheers,
    AI

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: IF , VLOOK formula help

    The formulas I gave are dependent of production descriptions in column G, and the selections for each group being made at top.... the way to avoid that problem is to have every cell in column G have a selection made.. (no blanks).

+ 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