Referencing Dots and Status in another worksheet
I need to reference these two columns from sheet B onto Sheet A.
I want to display worst case from sheet B on Sheet B.
Example: Project 1 on sheet A will display the worst case (At Risk and Red dot) from all rows on sheet B.
What are the two formulas I need? Attempted using an Index formula but was unsuccessful.
Answers
-
Hi @Engineo
Are you only wanting to show one value from each column, if it exists? In this case I'd actually use a COUNTIF statement to see if the Red Health ball exists at all in this column (if the count is greater than 1), then display red.
You can do this with each of the values in a Nested IF statement, like so:
=IF(COUNTIF({Health Column}, "Red") > 0, "Red", IF(COUNTIF({Health Column}, "Yellow") > 0, "Yellow", "Green"))
and
=IF(COUNTIF({Status Column}, "At Risk") > 0, "At Risk", IF(COUNTIF({Status Column}, "In Progress") > 0, "In Progress"))
Let me know if that makes sense or if i can explain anything further.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. - thank you. I attempted utilizing the formulas you provided and am getting the #unparseable error. I imagine this is a formula formatting error on my part but am unsure what I am still doing incorrectly.
Here is the formula as I entered it. Any ideas what I have done wrong?
=IF(COUNTIF({Sheet B Range 2}, “Red”) > 0, “Red”, IF(COUNTIF({Sheet B Range 2}, “Yellow”) > 0, “Yellow”, “Green”))
Sheet B is the actual name of the sheet being referenced
Range 2 is what Smartsheet yields when I choose the health column on sheet B
-
Hi @Engineo
How did you write the formula? It looks like the quotes you have pasted here are the wrong type of quotes for Smartsheet. For example, if you type a formula into a Smartsheet cell, the quotes will appear straight up and down like this: "
In your formula I see angled quotes like this: “
Try deleting out each quote and re-typing it directly into the Smartsheet cell. Let me know if it works now!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Thanks so much. It took me a little trial and error to get them all to work but your general format was perfect. I think most of the issues are just me learning Smartsheet.
The master sheet is updating just like I want it to now!!!
-
Oh good, I'm glad to hear that! 🙂
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.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!