cell reference as part of a workbook name

[keeponlearning]

Hi guys

I'm a newbie but your forum has given me a lot of great resolutions. But I'm stuck and can't find anything that address my problem.

First, macros and vba are not allowed in my office.

I will use an example to describe the issue but a can't use the actual workbooks due to confidentiality reasons.

In my example I have 2 workbooks; each containing all 12 periods of financial data. The 2 workbooks are for 2 separate companies with different fiscal years ends. Let's say Co A has a fiscal Y/E of Aug 2015 (Sep 2014-Aug 2015) so the workbook is named "CoA 2015" and Co B has a fiscal Y/E of Feb 2016 (Mar 2015 - Feb 2016) so the workbook is named "CoB 2016".

I want to create another workbook to consolidate the data in the other 2 workbooks for each calendar month. For example, June 2015. But this month falls in Co A fiscal 2015 and Co B fiscal 2016. I have a workbook now but it hard codes the fiscal year in my formula. The formula would look something like {=SUM(IF(A1='CoA 2015.xlsx'!glacct,'CoA 2015.xlsx'!per_01,0))} in one column and {=SUM(IF(A1='CoB 2016.xlsx'!glacct,'CoB 2016.xlsx'!per_01,0))}. Please note that I had to use an array formula because sumifs give me a #REF error whenever I close any of the workbooks and "glacct" and "per_01" are range names

What I would like to do is to replace the year in the formula with a cell reference; that is instead of "A1='CoA 2015.xlsx!glacct" my formula would look like "A1='CoA [cell ref].xlsx!glacct". This way I can use it for future years without having to change references in the actual formula. This will also make it easier for anyone else using the workbook.

And sorry but I want to remind you that macros and vba are not allowed due to IT security reasons.

Thank you very much in advance.

 

[Aladin Akyurek]

Have a look at INDIRECT but it requires the target book to be open (i.e., the function does not work with closed books).

 

[keeponlearning]

Have a look at INDIRECT but it requires the target book to be open (i.e., the function does not work with closed books).

Yes, thanks.

That was one of the solutions I was able to find in the forum. But as you say the target book(s) need to be open. As there are other readers/users for the consolidated workbook I did not feel that having to open 7 or more workbooks just to be able to see the results in the consolidation was a viable option.

But thank you very much.

 

[Aladin Akyurek]

Yes, thanks.

That was one of the solutions I was able to find in the forum. But as you say the target book(s) need to be open. As there are other readers/users for the consolidated workbook I did not feel that having to open 7 or more workbooks just to be able to see the results in the consolidation was a viable option.

But thank you very much.

Given your pre-requisites, "macros and vba are not allowed due to IT security reasons", you might try using CHOOSE instead...

Example:

=SUM(CHOOSE(MATCH(A1,{"CoA 2015","CoB 2015",...},0),'CoA 2015.xlsx'!per_01,'CoB 2015.xlsx'!per_01,...))

 

[keeponlearning]

Given your pre-requisites, "macros and vba are not allowed due to IT security reasons", you might try using CHOOSE instead...

Example:

=SUM(CHOOSE(MATCH(A1,{"CoA 2015","CoB 2015",...},0),'CoA 2015.xlsx'!per_01,'CoB 2015.xlsx'!per_01,...))

Perhaps I have gotten you offtrack. I imagine your formula does what I want to do. What I am trying to achieve is to replace the years with a cell reference. That is using your formula as an example ........{"CoA [A1]","CoB [A2]",....} ........ - A1 and A2 would be cell references. So I could put 2015 in A1 and 2016 in A2, etc. This way all I have to do is put the year I want into the cell and the desired workbook would be linked. All the workbooks for each company will have their own path. That is, CoA 2015, CoA 2016, etc would be in one folder CoA. And the same for CoB, CoC, etc.

I am sorry but it is getting late for me so can we continue this tomorrow. (is there a sleepy emoticon??? )

I do want to thank you for taking the time so far and all your efforts. This one really has me stumped.