Feb 14 2022 10:46 AM
I have 200+ excel workbooks linked to one excel workbook, call it "Databook". The method that I utilize for linkage is Index-Match.
I've set the links to Datebook such that the number of rows are far more than what Databook currently contains, allowing for me to expand the number of rows with Databook without having to make any changes to the 200+ workbooks. However, it's far more complex with regard to columns. If I insert a column within Databook, each of the 200+ workbooks require maintenance to reestablish the accurate linkages. I tried "naming" a column, but the Index-Match function would not recognize the name.
I would like the ability to insert a column within Datebook, without having to open up all 200 files and reestablish the proper linkages. Does anyone have a method that might work for me.
Feb 14 2022 01:51 PM
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
Feb 15 2022 05:54 AM
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?
Feb 15 2022 08:24 AM
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.
Feb 15 2022 08:37 AM
Feb 15 2022 09:17 AM
That's helpful. BUT, given that description, I wonder why it still is the case that you're adding columns to the master database. How many more nutrient profile attributes can there be after you've done the amount of work involved in getting to 1,600 recipes?! I can understand how it grew in the early stages, but surely by now the range of possible attributes for nutrient profiles of ingredients is pretty well established. Isn't it?
And if something new DOES get added, it would apply to a small subset of the recipes anyway, so could be incorporated in one or two of the subordinate workbooks.
Frankly, the other thing that occurs to me is that this whole process might be better approached as an Access database, or some comparable database manager. Excel is wonderful for what it does; your application is (I suspect) on the periphery of Excel's ideal applications. That it has worked thus far is wonderful.
The other possibility: do you use Power Query? I work solely in the Mac environment, which does not have access to Power Query--it's only in the Windows world. My understanding is that PowerQuery (in the hands of a master, at any rate) gives Excel a better collection of the powers of a relational database. If you're in Windows, if you keep your office software up-to-date, let us know. Some of the other regular contributors here may have some PowerQuery solutions for you,
Feb 15 2022 09:46 AM
Feb 15 2022 10:43 AM
Do you actually produce the recipes (i.e., the output) themselves in Excel? Given that a written recipe (aside from the list of ingredients) is mostly what I'd consider more suited to a word processor, I'm just curious....Again, I can see how Excel could be used, although it's almost a matter of "abuse" of Excel, and given Word's pretty impressive abilities at formatting.
Feb 15 2022 11:23 AM