Help with Formula / INDEX to show previous month's data

Options

Hoping to get a little help with a formula / Index set up. I found a scenario/question similar to what I am needing, but I can't seem to get it to work. The scenario I found provides =INDEX(January@row:December@row, 1, MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1))

Think it's the INDEX portion of the formula that is causing my trouble (Haven't worked with INDEX or VLookup before).

The columns on my stats sheet are set by our fiscal year Jul 20, Aug 20, etc . The formula needs to pull the previous month's data to be displayed on a dashboard (Metric Widget) and update automatically each month. So when looking at the dashboard this month (Feb), it will show January's data.

Could someone show how this would need to be set up to pull the data correctly?

Any assistance would be greatly appreciated!!

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Assume your widget sheet has the measure and last month's value columns. Place this in the last month value column:

    =VLOOKUP(Measure@row, {measure range}, IF(month(today())>=8, Month(today())-6, month(today())+6)) where the {measure range} is the table you attached with the measure in column 1 and July 20 to Jun 21 are columns 2 - 13.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!