Hello there,

I found what I thought was a great solution to my issue on this site, but now realize that it's missing something.

WHAT I NEED TO DO: Create a template that is populated by formulas that reference cells in another tab, which contain the names of other Excel files. The tables that I'm trying to compare are different versions of one another, like one that was first generated in Q1 and the next in Q2. The calculations are simple and I want to be able to drag the formula across that would more or less subtract the 2012 value of Q1 from the 2012 value of Q2, each in their own separate sheets. However, sometimes when the year changes, the 2012 value of Q1 might be in column D while the 2012 value of Q2 is in column E. So, I wanted to include a reference to a cell in the template that specifies what column to start in. This is what I ended up with:

=(INDIRECT("'["&readme!$B$5&"]P1'!"&readme!$C$5&"10")-INDIRECT("'["&readme!$B$4&"]P1'!"&readme!$C$4&"10"))

Where the B4 and B5 values are the names of the two other Excel files containing data, and C4 and C5 currently contain "D" and "E". However, as I realized when I dragged it across, the $ signs will make it so that every formula in the row will only reference D and E, instead of what happens when you normally drag a formula that doesn't have $ signs around the column names, which is to say, it would then apply to the next column to the right, and the next column to the right, etc. I'd like to drag the formula across so that it starts in the column I specify but then continues from there to compare the 2013 values in each sheet, then 2014 values, etc.

I'm thinking maybe I can just set up an index that looks for the year in the designated top row or something, but I'm not sure how to go about that within the framework of the indirect.

Any thoughts you have are GREATLY appreciated!! I have had to manually create these comparisons like 5 times already for 30 tabs each time, so I'm hoping having this template where I can just specify the names of the files I need to compare will make it much much easier in future instances!

Thanks,
Renee