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

  • Engineo
    Engineo ✭✭

    @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

  • Engineo
    Engineo ✭✭

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!