Formula for Health based on Week

r0030

Hi everyone - I have a Project Plan with an associated Master Tracker & Metrics Sheet. I am wanting to pull in the Health (RYG symbol column) based on the week. An example of my Project Plan is below:

You'll notice I have a "Week (Helper Column)" which helps me with calculating which week a project is at any given time. I'd like to broaden that formula a bit to include the week AND health. An example of what this Master Tracker/metrics looks like is shown below:

So, I'm only tracking one row per client and I need the week and health to be dynamic and adjust based on the start and end dates in the project plan. Any ideas?

Thanks so much in advance!

Dan W

I'm not sure if this helps. I don't know of a way to add the symbol to a none symbol type column. But this will add the Text version I.e "Green", "Red", "Yellow" to your formula. Just add this to the end.

+ " " + [Health]@row

This will add a space then the health. You can Put something between the " " such as - or / if you like and that will add it between the week number and the text.

r0030

Unfortunately I'm receiving an Unparseable message.

Could I make this a symbol column to pull in the symbol instead of the text? I'm just still not sure how to make it dynamic to the week that we're currently on in the Project Plan.

Appreciate your response!

Dan W

I'm sorry I misunderstood. What is the criteria for the health? and the goal is to have the health to change with which column?

r0030

No worries at all - I probably wasn't the clearest in my explanation :) I have two main sheets - Project Plan & Master Sheet with Metrics. I'm capturing Health in the Project Plan using the below formula:

I'm then pulling in health from the above Project Plan into my Metric Sheet. On the Metric Sheet I am capturing Week based on the start and end dates on the Project Plan. I'd like to create a formula to pull in the health based on the week that we're in.

Genevieve P.

Hi @r0030

You can use an INDEX(COLLECT formula to pull in data from another sheet based on multiple conditions (e.g. the start date and the end date)

For example:

=INDEX(COLLECT({Health Column}, {Start Date column}, =[Start Date]@row, {End Date column}, =[End Date]@row), 1)

See: Formula combinations for cross sheet references

However if you're looking to bring two points of data into one cell (e.g. Week and Health) then you could use an IF statement and Emojis to return an emoij along with your text value (since otherwise the symbol will show as text).

Let me know if this has helped or if we're still not quite understanding your end goal!

Cheers,

Genevieve