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:
The task sheets are as follows:
Bridge Building Sheet
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!!
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"]).
Please help. Thank you in advance!!
