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

@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 retyping it directly into the Smartsheet cell. Let me know if it works now!
Cheers,
Genevieve

@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! 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!