+ Reply to Thread
Results 1 to 4 of 4

How do I populate a table based on multiple criteria

  1. #1
    Registered User
    Join Date
    09-24-2014
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    8

    Question How do I populate a table based on multiple criteria

    I have a list of inventory,'Register', with columns "Quantity on hand" & "Minimum Stock Level" as my criteria. I have a formula that I thought would work but in every cell it pulls the same cell. I want another table, on another workbook, to populate everything from the 'Register' if it meets the following criteria.

    Criteria 1 (ignore If "Minimum Stock Level" is blank), Criteria 2 (If "Quantity on hand"<="Minimum Stock Level").

    I know how to do this based on the criterias, however, the way I know how would not ignore blank cells and therefore I would have to filter out the blank cells. another person, not so familiar with excel, will be using this table to approve orders.

    I hope I am clear.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: How do I populate a table based on multiple criteria

    You could use column Q as a helper column, with a formula like this in Q3:

    =IF(K3="","-",IF(J3<K3,MAX(Q$2:Q2)+1,"-"))

    Although you can't see this very well in your sample data as you only have one record which matches the criteria, this formula will set up a unique sequential number for each record which matches the criteria. It is then relatively easy to use an INDEX/MATCH formula in your other sheet to get the data across, e.g. this in B3:

    =IFERROR(INDEX(Register!A:A,MATCH(ROWS($1:1),Register!$Q:$Q,0)),"")

    You can't quite copy this across as you have some fields missing, and a few extras, so you will need to amend the Register!A:A part to suit the column where you want to get the data from. Then you can copy the formula down a required.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    09-24-2014
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    8

    Re: How do I populate a table based on multiple criteria

    This works great! thank you! i have one more question though. if the item is blank in the 'register' I how can I adapt the formula to show blank instead of 0?

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: How do I populate a table based on multiple criteria

    You can do it explicitly, like this:

    =IFERROR(IF(INDEX(Register!A:A,MATCH(ROWS($1:1),Register!$Q:$Q,0))="","",INDEX(Register!A:A,MATCH(ROWS($1:1),Register!$Q:$Q,0))),"")

    then copy across and down, but it is probably easier to apply a custom format to the cells of:

    general;general;;

    which will suppress any zero values.

    Hope this helps.

    Pete

    P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Populate data in a table based on dropdown criteria
    By Ragsbr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-08-2014, 03:23 AM
  2. [SOLVED] Populate table based on criteria from another table
    By m4k3n5h1 in forum Excel General
    Replies: 1
    Last Post: 03-29-2014, 12:55 AM
  3. [SOLVED] Automatically populate a table with slected data based on criteria
    By smithrog in forum Excel General
    Replies: 7
    Last Post: 07-01-2013, 03:52 AM
  4. Replies: 4
    Last Post: 09-07-2011, 05:49 PM
  5. Populate Data Based on Multiple Criteria
    By Kumara_faith in forum Excel General
    Replies: 4
    Last Post: 01-31-2011, 09:52 AM

Tags for this Thread

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