Dynamic Dashboard based on the month/date

ArthurC

I have a monthly sheet of various stats that get collected on a monthly basis. Therefore I have my columns as Jan/Feb/March etc. and my rows as my various measures.

what i need help with is, based on the current month (march), show me the previous month's values. so that when i look at the dashboard now, i see february stats, and in april, it will automatically show me March's stats.

I get that there needs to be an additional column with some month() formula, which i can't figure out, but then how will you select the column you need.

I'm thinking a =vlookup(A1:F10,month(),false). something like that where month() would return the month value, and then vlookup that many columns?

Paul Newcome

You are on the right track with a helper column. Here is a breakdown for an idea for a formula that will look across the row to the previous month and pull that data.

To pull the first of the current month...

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Then we subtract 1 from that to get the last day of the previous month...

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

Now we use a MONTH function wrapped around the generated date to pull the month number for the previous month.

=MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

Once we have that month number, we can use it in an INDEX function.

=INDEX(range to pull from, row number, column number)

We set the "range to pull from" as the current row covering the January through December columns.

=INDEX(January@row:December@row, row number, column number)

Since we are only referencing a single row, we can set the row number to 1 and use the previously generated last month's month number to tell it which column number to pull from.

=INDEX(January@row:December@row, 1, MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1))

Give that a go and see if it works for you.

Paul Newcome

You are on the right track with a helper column. Here is a breakdown for an idea for a formula that will look across the row to the previous month and pull that data.

To pull the first of the current month...

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Then we subtract 1 from that to get the last day of the previous month...

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

Now we use a MONTH function wrapped around the generated date to pull the month number for the previous month.

=MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)

Once we have that month number, we can use it in an INDEX function.

=INDEX(range to pull from, row number, column number)

We set the "range to pull from" as the current row covering the January through December columns.

=INDEX(January@row:December@row, row number, column number)

Since we are only referencing a single row, we can set the row number to 1 and use the previously generated last month's month number to tell it which column number to pull from.

=INDEX(January@row:December@row, 1, MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1))

Give that a go and see if it works for you.

ArthurC

that was very insightful. thank you very much for the detailed breakdown of the formula

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response as "helpful" so that others searching for a similar solution can know that one may be found here.