Excel 2013, Indirect #ref! Multiple workbooks and a need to use concatenate


New Member
May 4, 2016
So - I've read through a lot of the forums and online google searches and am having difficulty finding a solution I can apply. The example is: Workbook A has a summary page with a list of options to choose from. The user can populate Column A with a time series (12:00 am to 11:59 pm, for example), and then ask to find all associated data for that time period. The associated data is kept in multiple workbooks and multiple worksheets within those workbooks (I'm working with minute by minute data, 200 days of the year, 24/7 and already have issues with workbooks that operate slowly having created advanced functions used to fill gaps among 1440 rows of data each day of the year). Now, I've set up the other workbooks to already organize the data into similar columns to simplify everything (they already parse out 12:00 am to 11:59 pm, so the reference worksheet has no "calculations" to do - it only has to lookup values). So - The user has opted to display 1440 lines of data (12:00 am to 11:59 pm) for ... 4/22/2016, for example. Now, I've set up helper columns (CC) and (CD) with concatenate (respectively):
=CONCATENATE("'D:\foldername\foldername\foldername\foldername\2016\[Workbook B]"&$C$2&"'!$N$6:$N$1445")
=CONCATENATE("'D:\foldername\foldername\foldername\foldername\2016\[Workbook B]"&$C$2&"'!$P$6:$P$1445")
Where, in Workbook A, cell C2 is the day extracted from the date (which in this case is simply, 22), and in Workbook B, the N:N range of cells have been populated with corresponding times, and the P:P cells have been populated with the data to be referenced. The reference code in column B of Workbook A looks like this:
Where $A6 is the Time (identical to the N:N times in Workbook B). What do I get:
12:00 am#Ref!
11:59 pm#Ref!


Here's the catch, if I simply put the following formula into column b, it works with the closed workbook:
=IF($A6="",0,SUM(IF('D:\foldername\foldername\foldername\foldername\2016\[Workbook B.xlsx]22'!$N$6:$N$1445=$A6,'D:\foldername\foldername\foldername\foldername\2016\[Workbook B.xlsx]22'!$P$6:$P$1445,0)))

Of course - the simple answer, indirect doesn't work with closed workbooks. Argh (and I've opened up the workbook to ensure that my indirect code works when Workbook B is open - and it does). So - the code is right. All the workbooks are in the same folder even though excel keeps put in the entire directory name - so I stay with that.

The problem: I can't use static directory and file names unless I want to create 200+ days of the year, each one on a separate worksheet of Workbook A, parsing out all 1440 rows of data for each day, that can then be referenced by a summary page. That would be huge and unusable (excel is already slow with the multiple sets of data I have).

The solution needed: I have to somehow be able to dynamically link in a range of user entered times and dates, giving the user the option to reference different sets of data (contained in the different workbooks) they want shown for those dates and times - but I clearly can't use any dynamic system whereby I have to "concatenate" the data location since that still requires an indirect function. This is Excel 2013 and I don't have access to an older version, so the Morefunc and indirect.ext is not available. I'm not good with VBA, but am willing to give it a try (although honestly, I still prefer in-sheet formulas for my small, pea-brain).

Mind you ... once I solve this problem I'll be looking to add dynamic charts and graphs and get everything online - which I'll browse through the forums again at that time! I've considered Javascript (even though I know nothing about it), but am having difficulty finding an answer there, too.

Any thoughts?

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back