Index Matching Financial Data on Different Timelines

Options
Brett Robinson
edited 12/09/19 in Smartsheet Basics

Our company works on projects that run on different timelines than our fiscal year. Our fiscal year is a calendar year but we may have a project whose base year runs from August 2019 to August 2020. However, we need to report financials on both timelines. I've been working on building a financial tracking sheet to pull the data on both timelines but have run into an issue using index/match to pull the data based on fiscal year. 

Each contract year (i.e. Base, OY1, OY2, etc.) has certain financials categories: target, budgets, actuals, and projected. The issue I'm having is getting the fiscal year (which reports on the same financial categories) to pull the right row (target, budgets, etc.) base on which fiscal year month (current the columns). I attached some screenshots of the set up since it's tough to explain. The only thing I can think of is flipping the Months to run vertically so the rows have unique identifications so that index match is not going to find the first row that matches. For example, right now if I want actuals for September 2018, it will actually pull actuals for September 2017. 

I don't want to flip the rows and the data will be copied from another system that reports the months horizontally. Thanks for any help/ideas in advance. 

Screen Shot 2019-07-19 at 5.33.11 PM.png

Screen Shot 2019-07-19 at 5.31.14 PM.png

Comments