+ Reply to Thread
Results 1 to 7 of 7

Compare a range of cells to a specific cell, if match, compare another cell on same row

  1. #1
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    180

    Compare a range of cells to a specific cell, if match, compare another cell on same row

    Could someone please tell me if what I am trying to do is even possible. I have attached a spreadsheet as a reference.

    Sheet 1 will have new names added on regularly and can be duplicates but will have different Accounts. I need the "Valid" column on sheet 2 to use a formula that will match A2 and B2 on sheet 2 to the correct row on Sheet 1 with the same information. Once the row is determined on sheet 1, to take the "Date" column C2 on sheet 2 and determine if is greater then or equal to sheet 1 column c of that row and less then or equal to sheet 1 column d of that row. If those statements are true, to insert "Active" in D2 on sheet 2 and "Inactive" if false.

    Is this even possible??? Please help...I am completely stuck
    Attached Files Attached Files
    Last edited by Dena; 05-30-2012 at 02:13 PM.

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

    Re: Compare a range of cells to a specific cell, if match, compare another cell on same ro

    If there will be only one unique name/dept combination in Sheet1, then you can use:

    =IF(AND(C2>=SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!B:B,B2),C2<=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,B2)),"Active","Inactive")

    copied down.
    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
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Compare a range of cells to a specific cell, if match, compare another cell on same ro

    Thank you for this, it worked like a charm. The only problem I am having is when I use the Filter option. I currently have it turned on and if I filter sheet 2 column A, the "valid" column on sheet 2 populates different results. I know I can use the $ symbol but where should I place it to prevent the data changing when I use the filter.

    Thank you

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

    Re: Compare a range of cells to a specific cell, if match, compare another cell on same ro

    I am not sure what you mean... If you filter by column A all the other data in the rows should hide/show simultaneously.

    Can you show a sample workbook with some more data in it, and let us know what is going on.

  5. #5
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Compare a range of cells to a specific cell, if match, compare another cell on same ro

    I have attached my actual spreadsheet. I am trying to filter "Student Worker Pay" column A by A to Z but when I do that, the data in column D labeled "Valid" changes. The way it is attached is accurate but when I perform the filter, "Briana Ruiz" data changes. Does the same if I sort by Date, some of the data changes.

    Am I doing something wrong.12-13 budget.xlsx
    Last edited by Dena; 05-30-2012 at 02:15 PM.

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

    Re: Compare a range of cells to a specific cell, if match, compare another cell on same ro

    That's sorting not filtering...

    Anyway, just take out all the sheetname references to the sheet you are in.. I.e. take out all the references to "Student Worker Pay"


    =IF(A2="","",IF(AND(C2>=SUMIFS('Student Worker List'!C:C,'Student Worker List'!A:A,A2,'Student Worker List'!B:B,B2),C2<=SUMIFS('Student Worker List'!D:D,'Student Worker List'!A:A,A2,'Student Worker List'!B:B,B2)),"Active","Inactive"))

    copied down.

    Notice that I added a bit in front to clean up the column a bit...

  7. #7
    Forum Contributor
    Join Date
    05-29-2012
    Location
    Walnut, Ca
    MS-Off Ver
    Excel 2010
    Posts
    180

    Re: Compare a range of cells to a specific cell, if match, compare another cell on same ro

    Thank you, worked like a charm. It is greatly appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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