Formula to return data based on text in another field (same row) but different sheet



Is it possible to write a formula to recognize specific data from a text field and return data from another field in that same row but in a different sheet?

For example, I am putting together a dashboard to display the health, trend and status of the 6 top summary tasks in my schedule. I'd like a formula that would identify "1. Quality and Variety" (in the Task/Primary field) and then return the health color symbol in the "Health" field. If I am writing the formula on the same page using the specific row I can make it happen but if I am trying to write the formula on a different sheet to use for my dashboard, it doesn't work.

Is this even possible? And if so, how would I do it? Thank you in advance.

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @lisamurzyn

    It actually sounds like a Row Report may be the easiest way to do this!

    You could set up a filter to only show the specific rows you want (e.g. "1. Quality and Variety") and select the relevant columns to show along with that data (e.g. "Health").

    Then you can add the Report as a Report Widget on to your Dashboard. If this won't work for you, it would be helpful to see screen captures of your current set-up but please block out sensitive data.



    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!