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

lisamurzyn

Hi,

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.

Genevieve P.

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.

Cheers,

Genevieve

Andrée Starå

Hi @lisamurzyn

I hope you're well and safe!

Are the Task/Primary filed unique, or do you have something else that is unique that we could use to connect the sheets?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

lisamurzyn

Hi! The contents in the text field are unique. I've tried linking the cells to a different sheet but am concerned that if I add more rows it will not find what I'm looking for.

Lisa

Genevieve P.

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.

Cheers,

Genevieve

lisamurzyn

Hi!! Well, this worked perfectly! I really appreciate the help!

Lisa

Genevieve P.

Wonderful! I'm glad it worked for you 🙂