Referencing (the same) cell in a closed book for different Monthly versions

[garethwilce]

Hello

If:

• In folder "Jan 2016", I had an excel workbook named "File A", which has a worksheet called "Entity 1", and within this worksheet, cell A1 contains a value "10".

Then:

• In Folder "Feb 2016", I had the same excel workbook (named "File A", which has a worksheet called "Entity 1"), but within this worksheet, cell A1 contains a value "20".

And:

• This is repeated this for folders for each month of the year, so that

"Mar 2016", cell A1 = "30"
"Apr 2016", cell A1 = "40"
etc

Question:

• Within a separate "Summary" workbook, how can I create a dynamic formula to pick up cell A1 from the file which I select (Say, "Sep 2016" = "90")?

• I have tried to build a dynamic INDEX formula, which would reference cells in the "Summary" workbook that I would amend to select the month and year (eg. Month = Jan, Year = 2016), but I cant seem to get it to work.

 

[arthurbr]

Does this put you on track Copy a range from closed workbooks (ADO) ?

 

[garethwilce]

Yes, I can see how that might work by using Examples 4 and 5.

However rather than use a macro, I was hoping that I'd be able to construct a dynamic formula so that by inputting a (1) month and (2) year into certain control cells, this would amend the pathway to select a certain file. Do you think that this is possible?