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

NJR

New Member
Joined
Sep 12, 2021
Messages
2
Platform
  1. Windows
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 TypeName
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")
BridgeABC
If I select CDE...
YES
Then this should read NO, per sheet below
RoadSelect personCalculator looks at the Road Sheet for selected individual having completed the task with qualifier

The task sheets are as follows:
Bridge Building Sheet
DateProblem descriptionIndividual
This is set up as a drop down with initials
Qualifier
This is set up as a drop down with Yes/No
1Jan21Built bridge across creek XABCYES
1Jan21Built bridge across creek yCDENO
5Jan21Build bridge across creek zCDENO
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"]).

Please help. Thank you in advance!!
 

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,820
Office Version
  1. 365
Platform
  1. Windows
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")
 
Solution

NJR

New Member
Joined
Sep 12, 2021
Messages
2
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,820
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
:)
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top