Need to have a dashboard show only Current Month status but different column for each month

Corey McCarren

Hello! To explain:

We have a goal-setting Smartsheet template. In the template, there is column for each month (the row is the goal)

So, for row #1 (the first goal), there will be a column Q1J (Jan), Q1F (Feb), Q1M (Mar), Q2A (April) and so on.

The end users I want to only fill out the column for the month we are in, and the past months is historical data. The status will be either Green, Yellow, or Red for a given month.

I want only the current month to show up as Green, Yellow, or Red on a dashboard for each goal. I do not want end users to have to update the dashboard each month.

So, what I am envisioning is a Helper column, with a formula that says something like: "If current month is October, make status the same as column Q4O, if current month is November, make status the same as column Q4N" and so on and so forth.

That way I can tie only the helper column to the dashboard, and it will always be relevant to the current month.

Any ideas? Thank you for any and all guidance. This seems like a pretty complicated formula but it will be very impactful for our org's goal tracking.

Image to help:

DKazatsky2

Hi @Corey McCarren,

Here is one solution I have come up with, but requires a redesign on your part. Instead of having the goals in rows and the months in columns, I have reversed it to make the helper an easier formula.

Here is the how the main sheet is laid out.

The formula in the "Current Month?" is as follows. =IF(MONTH(TODAY()) = [Month Num]@row, 1)

Next create a report and filter on the "Current Month?" field.

Lastly, add a report widget to the dashboard.

I'm not sure if this solution will fit your needs, but maybe will give you some ideas if not.

Hope this helps,

Dave

Corey McCarren

Hi @DKazatsky2

It is a really creative solution, thank you for the time taken to come up with it! Unfortunately I don't have the option to swap rows and columns for this to work, but it definitely opens the thought process to other ideas.

Corey McCarren

I am trying this, although it does not work either. I think it might be sort of close:

=IF((MONTH(TODAY())=1, [Q1J]@row, 1), IF(MONTH(TODAY())=2, [Q1F]@row, 1), IF(MONTH(TODAY())=3, [Q1M]@row, 1), IF(MONTH(TODAY())=4, [Q2A]@row, 1), IF(MONTH(TODAY())=5, [Q2M]@row, 1), IF(MONTH(TODAY())=6, [Q2J]@row, 1), IF(MONTH(TODAY())=7, [Q3J]@row, 1), IF(MONTH(TODAY())=8, [Q3A]@row, 1), IF(MONTH(TODAY())=9, [Q3S]@row, 1), IF(MONTH(TODAY())=10, [Q4O]@row, 1), IF(MONTH(TODAY())=11, [Q4N]@row, 1), IF(MONTH(TODAY())=12, [Q4D]@row, 1))

Corey McCarren

I think I have figured it out thanks to David, a bit of AI, and then a bit of troubleshooting:

=IF(MONTH(TODAY()) = 1, [Q1J]@row, (IF(MONTH(TODAY()) = 2, [Q1F]@row, IF(MONTH(TODAY()) = 3, [Q1M]@row, IF(MONTH(TODAY()) = 4, [Q2A]@row, IF(MONTH(TODAY()) = 5, [Q2M]@row, IF(MONTH(TODAY()) = 6, [Q2J]@row, IF(MONTH(TODAY()) = 7, [Q3J]@row, IF(MONTH(TODAY()) = 8, [Q3A]@row, IF(MONTH(TODAY()) = 9, [Q3S]@row, IF(MONTH(TODAY()) = 10, [Q4O]@row, IF(MONTH(TODAY()) = 11, [Q4N]@row, IF(MONTH(TODAY()) = 12, [Q4D]@row)))))))))))))

I suppose I won't know for sure though until December rolls around lol