+ Reply to Thread
Results 1 to 6 of 6

Grab lines of data from sheet based on multiple criteria (working slightly)

  1. #1
    Registered User
    Join Date
    09-13-2017
    Location
    England
    MS-Off Ver
    2010
    Posts
    16

    Grab lines of data from sheet based on multiple criteria (working slightly)

    Hi!

    I'm working on a formula to funamentally generate a live report based on data in a 'master' spreadsheet.
    I have a formula at the moment that works for one set of criteria, but I cannot figure out how to include another.

    Untitled.png
    This is the report as it stands, the red is blanking out confidential data, apologies.

    The current report is bringing forward all records that have the status "Pre-Approvals" in the master spreadsheet, hence why the ID (#) field does not go up in order, it's showing all the ID lines for Pre-Approvals, using the following array formula (in cell B3):
    Please Login or Register  to view this content.
    This grabs the ID of the Pre-Approval lines, and then the rest of the cells are populated using a VLOOKUP (array) entered into cell C3 but it applies to the rest of the row:

    Please Login or Register  to view this content.

    This works, as you can see in the screenshot - however, I need to also bring records into the report if they're "In-Approvals". I have tried so many different methods but cannot find a way to make this work!

    One formula I tried:
    Please Login or Register  to view this content.
    Another I tried was an =IF(OR( but I forgot the exact code I tried - it did not work though.


    I am aware that I could filter the master sheet and copy/paste the values but I need it to update live, i.e. if the status changes (in the master sheet) from In-Approval to the next stage, it should automatically be removed from the tracker, same with the records being added.


    In simple terms, I want the logic of:
    Please Login or Register  to view this content.
    I am doing currently:
    Please Login or Register  to view this content.


    Any help on this would be greatly appreciated!

    Many thanks

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Grab lines of data from sheet based on multiple criteria (working slightly)

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Grab lines of data from sheet based on multiple criteria (working slightly)

    Try


    =SMALL(IF(([Test_book.xlsx]Sheet1!$E$3:$E$341="Pre-Approvals")+([Test_book.xlsx]Sheet1!$E$3:$E$341="In-Approvals"),ROW([Test_book.xlsx]Sheet1!$E$3:$E$341)),ROW(1:1))-2

  4. #4
    Registered User
    Join Date
    09-13-2017
    Location
    England
    MS-Off Ver
    2010
    Posts
    16

    Re: Grab lines of data from sheet based on multiple criteria (working slightly)

    Quote Originally Posted by JohnTopley View Post
    Try


    =SMALL(IF(([Test_book.xlsx]Sheet1!$E$3:$E$341="Pre-Approvals")+([Test_book.xlsx]Sheet1!$E$3:$E$341="In-Approvals"),ROW([Test_book.xlsx]Sheet1!$E$3:$E$341)),ROW(1:1))-2
    This worked perfectly! Thank you so much! I've rep'd you

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Grab lines of data from sheet based on multiple criteria (working slightly)

    Thanks for the rep.

    FYI: the "+" is an OR condition and "*" is an AND condition

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

  6. #6
    Registered User
    Join Date
    09-13-2017
    Location
    England
    MS-Off Ver
    2010
    Posts
    16

    Re: Grab lines of data from sheet based on multiple criteria (working slightly)

    Quote Originally Posted by JohnTopley View Post
    Thanks for the rep.

    FYI: the "+" is an OR condition and "*" is an AND condition

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Thanks for that useful info. And yeah I already did that earlier

+ 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. Replies: 2
    Last Post: 01-23-2016, 01:16 PM
  2. Replies: 0
    Last Post: 07-13-2015, 01:19 AM
  3. [SOLVED] Grab Data from more than one column Based on Selection from List in a different sheet
    By Chantal4130 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-04-2014, 10:22 AM
  4. Grab text from another sheet based on data in current sheet.
    By Sean.Rooney in forum Excel General
    Replies: 5
    Last Post: 04-08-2014, 02:03 PM
  5. Replies: 0
    Last Post: 12-28-2012, 07:06 AM
  6. Replies: 6
    Last Post: 07-31-2012, 09:37 PM
  7. Create multiple lines based upon criteria
    By SystemsAccountant in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-05-2008, 01:14 PM

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