Formula to look at specific column/cell depending on the current month



I want to create a dashboard widget that will show employee FTEs for the current month. I currently have a sheet (Capacity Plan) that list out the employee's name, and then has the assigned projects with FTE breakdown as child rows underneath. Then I have row that calculates the total for the month.

For the widget, I would like to create a separate sheet with a formula that says "If the employee is Sarah and the month is January look at this cell in the JAN 23 column, If the employee is Sarah and the month is February look at this cell in the FEB 23 column..."

I wasn't sure if I could use an IF + VLOOKUP or INDEX/MATCH.

If it's no possible, I welcome any other suggestion to avoid having to manually change the cell link every month. It's not the end of the world, but the more I can automate, the better!

Thank you in advance!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!