I need help rolling up multiple weeks of data into one response based on specified criteria

Kishan Naik RA

I currently have a sheet that collects weekly attendance info from multiple stakeholders (for the purpose of this question we can just look at the Coach Info columns). There are 3 responses that can be put into this column: 'Yes', 'Some, and 'None'.

My second sheet is a monthly tracker in which I am trying to read four weeks of data from the weekly Coach Info column and spit out the highest level response with the specific criteria being the student name (if there is a 'Yes' in any of the four cells, take 'Yes', if no 'Yes' but there is a 'Some' then take 'Some' and if there are no 'Yes' or 'Some' responses then take a 'No'). I am trying to use an INDEX(COLLECT) formula but am only able to get one response and not the logic I described in the parentheses above. I am not sure if there is a way to add an IF(OR) statement or something similar but I am stumped. Below is the current formula I am using -

=IFERROR(INDEX(COLLECT({2024/25 Weekly Sports/Activity Attendance Range 1}, {2024/25 Weekly Sports/Activity Attendance Range 1}, OR(@cell = "Yes", @cell = "Some of them", @cell = "No"), {Weekly Attendance Info Range 2}, Student@row, {Weekly Attendance Info Range 3}, Month@row), 1), "No Data")

Screen Shot 2024-08-28 at 12.02.03 PM.png

Screen Shot 2024-08-28 at 12.02.17 PM.png

Screen Shot 2024-08-28 at 12.03.52 PM.png

Gillian C

Hi @Kishan Naik RA

It sounds like you want a nested if to give you the 3 different possibilities, however I'm struggling to work out what criteria you are needing to match and how your sheet is laid out. Is there a way for you to share a copy of your sheet with any sensitive information removed?

I could then have a look to see if I can help with the layout of your formula

Kishan Naik RA

Are you able to access the screenshots I uploaded? Essentially the criteria to match are the student name column and the month column. The weekly sheet (second screenshot) has hierarchy for each week with the same list of students and the first screenshot has a portion of week 1 uncollapsed to show the columns in the weekly sheet. The formula I shared above is in the sheet in the third screenshot and is trying to pull info from the coach info columns related to a specific student and month (four cells per student per month).

My issue is the formula is only pulling the first coach response it finds instead of reading all four weeks and picking the highest level response (Yes, Some of them, None of them). Let me know if that is helpful!

Gillian C

Hi @Kishan Naik RA

Thanks for the clarification, I understand better now. In order to look at all 4 weeks not just the first instance you will need to create a column that will show the instance of the student name and month. You will have potentially up to 5 entries per month. I have a similar formula that I'll pull out tomorrow for you and then using that column as a helper column I should be able to put together a nested if that will do what you have described above. Give me some time tomorrow and I'll get back to you on it.

Anything in the meantime just @ me so that I know you have messaged.

Kind Regards

Gillian

Kishan Naik RA

@Gillian C

I am glad the clarification was helpful! Take all the time you need to pull the formula, thanks so much for the help!