Index and Formula Reference to external files

[scruz9]

Hi All,

I built a model template that references files for financial figures. However, as I build-out forecasts, I will have to change the file reference. My thought was having some sort of table of contents that if a set cell has a date of X it references a specific file and changes the file path.

Luckily all my models are built the same way so my index formulas will be referencing the proper rows.

Unfortunately, since I have 3 different files it references. I would need somehow to dictate that this cell follows this table of contents.

So for example let's say in Summary Tab the date is '04/2016' .. the sheet Monthly Summary A2:F100 Follows a certain Modeland if dated changed it would reference another file. In that same sheet (Monthly Summary) H2:H100 follows all of our actual financials and each month has a saved down file. So on and soforth

[FDibbins]

Excel would use INDIRECT() for this, but that only works on open workbooks