• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IFERROR/AND/IF Functions across 2 worksheets

R

rsomist

Member
Hello!

I have two worksheets I'm working with and trying to find a function that will search down and across columns on one worksheet and populate an answer on the other worksheet. Here's my examples:

Sheet1
upload_2015-11-5_9-23-42.png


Sheet2
upload_2015-11-5_9-24-2.png

I'm needing a function in all cells down columns B, C and D that will populate an "x" if the auditable entry is listed on sheet1 and an NC if it is not. For example, on Sheet1, the "Application - Facets" auditable entity is sourced to both Claims QA and External Audit, so on Sheet2, there is an "X" in cells B2 and D2 for the Facets Application, but an "NC" in cell C2 because there was no internal audit sourced for this auditable entity on Sheet1.

I have tried this function:
=IF(AND('Tab 1'!$B3="External Audit",'Tab 1'!$a3="Application - Facets"),"X","NC") but it's not correct and I'm lost on where to go.

Thanks in advance!

Ross
 
As you haven't upload the sample so i couldn't tested


put in B2 & drag right/down!

=IF(COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1),"x","NC")
 
So maybe I'm not rewriting the function correctly either for columns C and D - Column B on Sheet2 is correct.
 
Sorry - I figured out my error in the other columns. This function works perfectly. Thank you so very much!

Ross
 
You must log in or register to reply here.
Back
Top