Checking another sheets Tick box and returning a value based of Row ID

Ben Hollins

Hi Helpful community!

Hopefully I've titled this correctly. I have two separate sheets, a master and a task sheet. I've been having trouble creating a formula that checks the "task" sheet and returns a value based of if a tick box is marked.

Main Sheet looks like this:-

The Formula i need is for the "Promo Materials" Field.

The Sheet its referencing looks like this :-

In essence, it needs to check if the "All completed" tick box is checked and return the value " Completed". If the RMID has a value, But the "All completed" box isn't checked, then the its "In Progress". Otherwise it should be "To Do".

I think i'm close with two of the options with the below. Just can't get it over the line

=IF(INDEX({Promo Completed}, MATCH(RMID@row, {RMI_ID Check}, 0)) = 1, "Completed", "Not Started")

Thanks for any assistance!

Paul Newcome

My suggestion would be to put the nested IF on the checklist sheet (column can be hidden if needed after setting it up) then just using an INDEX/MATCH on the Master Sheet to pull from this new helper column.

Ben Hollins

I played around with it yesterday and ended up resolving the issue. Not sure if its the prettiest solution but it appears to work.

=IFERROR(IF(INDEX({Promo Completed}, MATCH(RMID@row, {Promo RMI_ID Check}, 0)) = 1, "Completed", "In Progress"), "Not Started")

However it's still not ideal, as id still like it to show as Not Started if there are is an entry on the sheet, but nothing has started. At the moment, it shows any entry as "in progress" unless the RMI ID field is blank.

Paul Newcome

Did you try my suggestion?