Custom function which references external data

[deftones]

Parameters which can't change
I use 14 different data sets, which are stored in regression formulas (y=mx+b) in 14 different workbooks. This is equivalent to a couple of 10x10 values tables in each file. These data sets are referenced in ton of workbooks in a various tedious way. I want to streamline this.

My first thought (good thought I think), was to create a custom function which pulls from the appropriate data source based on parameters. I can then save this function as an excel add-in so its available in all workbooks which would use the reference. Bad idea - my mistake is the custom function uses data in closed workbooks, and although I can use vba to open these workbooks, get the data required, and close the workbook behind the scenes, this seems cumbersome and clunky when done on a larger scale.

My next thought is to somehow aggregate the data sets into one txt file that can be stored with the excel add-in. I have no experience in this. Any thoughts from those with experience?

Once implemented, this will affect multiple users over several years, so I want to make sure this is a good solution.

 

[bertie]

My next thought is to somehow aggregate the data sets into one txt file

Why not store your data in one of the worksheets of your add-in?

Remember, when you reference an add-in worksheet sheet in the add-in code to use the ThisWorkbook object.
eg.,
Set myRange = ThisWorkbook.Worksheet("Data").Range("A1:A2") - this references a sheet named Data in the add-in.

 

[Teeroy]

Indirect.ext from the Morefunc add-in may work for you to access the closed workbooks, but it only works in 32 bit excel.
Here's a couple of recent links to find it Looking for Morefunc Excel Add-on for Indirect.ext (I haven't checked them).