# Reference another cell in a row referenced by"and" function

#### NJR

##### New Member
Hi there,

I'll start with what I'm trying to do as a whole and then explain what I've tried just in case I've gone down a rabbit hole:

I've got ten people, five of whom need to complete ten tasks; each task has a Yes/no qualifier. I am trying to create a tracker/calculator with dropdowns to select one of the five individuals and see if they have completed a task with a yes qualifier.

Current status:
Each of the tasks has its own page. Each page utilizes dropdowns to select individuals using their initials and a second "qualifier" column with a Y/N drop down.

On the first page with the calculator, I have set up a table as follows:
 Task Type Name This is set up as a drop down with initials. The formula that is driving me up the wall lol =IF((AND(Sheet2!D1="Yes", Sheet2!C2=Sheet1!G2)), "YES","NO") Bridge ABC If I select CDE... YES Then this should read NO, per sheet below Road Select person Calculator looks at the Road Sheet for selected individual having completed the task with qualifier

The task sheets are as follows:
Bridge Building Sheet
 Date Problem description Individual This is set up as a drop down with initials Qualifier This is set up as a drop down with Yes/No 1Jan21 Built bridge across creek X ABC YES 1Jan21 Built bridge across creek y CDE NO 5Jan21 Build bridge across creek z CDE NO ETC...

The issue I am running into is getting the calculator to search the entirety of each respective sheet, rather than just the selected row for a name and a yes. I had initially attempted to use isnumber(search), to try and search the columns for initials and a yes/no but this either resulted in syntax errors OR when I did get it to work, it more or less functioned as an "or". I.e. As long as a there was a Yes qualifier on a specific task page, and one of the respective five individuals was listed, the calculator produced a YES - Selecting person CDE in the calculator for Bridge tasks would result in a YES, but because of ABC having done it.

If it helps, the isnumber(search) looked something like this: =if('bridge building qualifier!="YES", [=if(=isnumber(search('bridge building qualifier','calculator name')), ["YES"], ["NO"]], ["NO"]).

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Joe4

Welcome to the Board!

Does this do what you want:
Excel Formula:
``=IF(COUNTIFS(Sheet2!C:C,Sheet1!G2,Sheet2!D:D,"YES")>0,"YES","NO")``

#### NJR

##### New Member
YESSS! Thank you; that did the trick!
Welcome to the Board!

Does this do what you want:
Excel Formula:
``=IF(COUNTIFS(Sheet2!C:C,Sheet1!G2,Sheet2!D:D,"YES")>0,"YES","NO")``
YESS!! Thank you; that did the trick!

#### Joe4

You are welcome.
Glad I was able to help!

Replies
21
Views
3K
Replies
0
Views
126
Replies
1
Views
168
Replies
4
Views
241
Replies
1
Views
489

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,837
Messages
5,766,721
Members
425,373
Latest member
ndiejennrrd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back