+ Reply to Thread
Results 1 to 3 of 3

Displaying data from multiple sheets with multiple criteria

  1. #1
    Registered User
    Join Date
    04-26-2021
    Location
    New Jersey, USA
    MS-Off Ver
    2016
    Posts
    3

    Displaying data from multiple sheets with multiple criteria

    Hello everybody,

    Is it possible to have 2 criteria of which either one can be met to return a value?

    =IFERROR(INDEX(GMF!D:D,AGGREGATE(15,6,ROW(GMF!$A$2:$A$250)/(GMF!$D$2:$D$250<31),ROWS(B$3:B3))),"")

    This function return a name and a number of day until a person is due for requalification when the cell display < 31 days in a different sheet. Is it possible to add a second argument where the same data will be returned if the cell either displays < 31 days or the word "Overdue" in it. The way the worksheet is done, once the counter hits < 1 day, instead of a number of days, the cell will display "Overdue" and because of that, the above function does not return a person's name anymore since the criteria is no longer met.

    If you look in the "Overview" sheet of the attached file, only 3 of the 4 names are pulled from the "GFM" and "FMF" sheets because the 4th name is no longer a digit, but a text of "Overdue". I am trying to get it to display the names and number of days < 31 along with any entries that are also Overdue as they are also due for requalification.

    Thank you for your help!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-03-2021
    Location
    Nashville
    MS-Off Ver
    MS Office 2016
    Posts
    59

    Re: Displaying data from multiple sheets with multiple criteria

    It will be easier to have a "helper" column when dealing with two conditional integers.

    "IF" formulas are always read from Left to Right and as soon as the formula is met with a "True" the string is no longer read and fills in the value.

    If you need two integers to meet a criteria, you may want to look at using the "AND" condition within an if statement.

    I need to open my work laptop to download your file to see what you are looking at doing. But a "helper" column may be the easiest and best way to accomplish what you are trying to do.

  3. #3
    Registered User
    Join Date
    05-03-2021
    Location
    Nashville
    MS-Off Ver
    MS Office 2016
    Posts
    59

    Re: Displaying data from multiple sheets with multiple criteria

    After downloading your doc
    The helper column if you want to give "Overdue" a value, you can move your array over the helper column to give it a value.
    For example on GMF, on column G just keep the formula without the if statement =C2-TODAY(), this will give you a value and will not make your people disappear. (you can then make the column text white or hide the column if you want to make the worksheet stay nice and clean looking.
    If you do not want a negative number you can surround the formula with =ABS(C2-TODAY())

+ 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. Using multiple criteria, extract data from multiple sheets into one sheet
    By kaziz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-29-2021, 02:20 AM
  2. [SOLVED] Merge multiple sheets data into one worksheet based on multiple criteria and date
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 02-03-2018, 04:19 AM
  3. [SOLVED] Merge multiple sheets data into one worksheet based on multiple criteria
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-05-2016, 10:32 AM
  4. Match & return data across multiple sheets, multiple criteria
    By mrsproctor in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-18-2015, 06:01 PM
  5. Grabbing data w/ multiple criteria across multiple sheets
    By smithj96 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2014, 02:49 PM
  6. Multiple Sheets Multiple Criteria Average Data
    By apauaie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2013, 01:13 PM
  7. Replies: 0
    Last Post: 07-27-2011, 01:00 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