Set a Checkbox Based on Color Status from Another Sheet
I'm trying to set a checkbox on sheet 1 based on the color status in sheet 2. For example, if the status on the 2nd sheet is "Blue," I want to check the box on sheet 1. Here is my formula:
=IF({Overall Completion} = "Blue", 1)
However, that generates a #INVALID OPERATION
Any suggestions?
Best Answer
-
Hi Geoff,
You would need some sort of other unique identifier... for example, a Row ID that is the same for both rows in the sheet. Then we could use a different formula, perhaps an INDEX(MATCH or a JOIN(COLLECT to return the checkbox, but it won't know which row to look at unless we have some sort of identifier.
As an alternative, what about using a cell-link for that coloured status cell, bringing in that column or cell to the current, second sheet? Then you could run a simple IF formula like what you have, except instead of a cross-sheet reference it would be looking at the cell in its own row:
IF([Overall Completion]@row = "Blue", 1)
If cell-linking won't work for your set up, it would be useful to see a screen capture of both sheets (but please block out any sensitive data) with an explanation of why the box is being checked.
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi Geoff,
You would need some sort of other unique identifier... for example, a Row ID that is the same for both rows in the sheet. Then we could use a different formula, perhaps an INDEX(MATCH or a JOIN(COLLECT to return the checkbox, but it won't know which row to look at unless we have some sort of identifier.
As an alternative, what about using a cell-link for that coloured status cell, bringing in that column or cell to the current, second sheet? Then you could run a simple IF formula like what you have, except instead of a cross-sheet reference it would be looking at the cell in its own row:
IF([Overall Completion]@row = "Blue", 1)
If cell-linking won't work for your set up, it would be useful to see a screen capture of both sheets (but please block out any sensitive data) with an explanation of why the box is being checked.
Thanks!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks Genevieve! The cell-link idea worked!
-
Hi Geoff, that's great! Thanks for letting us know. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!