Referencing Dots and Status in another worksheet

Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

  • Engineo
    Engineo ✭✭
    Options

    @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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

  • Engineo
    Engineo ✭✭
    Options

    @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!!!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Oh good, I'm glad to hear that! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!