Is there a way to dynamically reference between workbooks in Excel?

Question

I love the indirect formula, but I'm unable to reference another closed excel workbook without getting the REF error. Is there another formula or something that I can use that can dynamically reference closed workbooks? I need something that will allow me to use a cell input that rolls over the file to the next month's column in another workbook.

Background: I have two excel workbooks and the excel references need to dynamically move to the next month when I select which month as the input. So far, I've only been able to successfully do this within the same workbook with the indirect function. Please advise.

Answer 1

You could use an INDEX with a MATCH formula nested within it or VLOOKUP to get the data you are looking for.

You could add a helper column to make your referencing easier, but unless there is some sample data to create a formula, creating a formula that works is a difficult task.