Checking another sheets Tick box and returning a value based of Row ID
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!
Answers
-
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.
-
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.
-
Did you try my suggestion?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!