Forum Discussion
Linking Workbooks
Ever since watching the linked YouTube video on Dynamic Array Functions (notably FILTER) I've used FILTER to do -- on a far less ambitious basis -- what you're doing, or at least what I understand you to be doing. I download a datasheet from my financial services provider and then bring ALL the data in to a single workbook with but a single FILTER formula. It dynamically fills all the rows and columns that are present in the data source.
You sound like a very savvy user, so I have no doubt you'll be able to adapt this IF it does fit your need, and if not, you'll recognize that too.
https://www.youtube.com/watch?v=9I9DtFOVPIg
First of all, let me say how appreciate I am that you would take the time to respond. I watched your video. The filter function does appear to me to be a more efficient method than is than the Index-Match method that I am using.
However, your examples are all using the filter function within one workbook. My struggle is that I'm using these functions between separate workbooks. I have 200+ workbooks pulling data from 1 workbook. Within that one workbook, I would like the ability to insert a column without corrupting all the linkages within the 200+ workbooks. Does your video address that problem? Or this there another method out there that could be of help to me?
I use more than one workbook to pull data from the single source; just not 200!
And it is possible to insert new columns and have FILTER accommodate that. It gets tricky, and how best to adapt would be a function of how frequently those changes are made. So why don't you describe a bit more completely here the bigger picture.
- Maybe start with explaining why there need to be 200 "subordinate" workbooks? The fact that there are 200 workbooks each dependent on that single source of data, yet independent of each other, is certainly a major complication. Why can't they be, perhaps, 200 separate worksheets within a single dependent workbook? [that, actually, is more descriptive of my situation: I have separate worksheets in the workbook that derives its data from the source workbook by means of FILTER]
- And then add to that explanations of the frequency with which new columns get added; does the count of rows change too? How frequently, etc.
- What's the nature of the data? Text? Numbers? Currency? Test results....
- Happy to provide more context. The master workbook is a database of ingredient nutrient profiles. The 200+ workbooks are recipes. Each of those workbooks has 8 tabs already, so the idea of bringing them all into one workbook, with 1600 tabs or more seems daunting to me.
