Forum Discussion
Pull through info from individually named sheets
Definitely doable. BUT your description suggests that there could be ways to make the job a lot more reliable. This one statement, for example, popped out to me:
Many of these columns will be in different column letter in the individual workbooks since there is no uniformity in structure but the name of the columns e.g. PO# will be standardized.
If the column headings can be standardized, why not the entire structure? Numbers of rows could vary, but a consistency in column organization, proper use of tables, etc.: why can't that be standardized?
I have a master workbook that pulls data from subordinate sheets, but I do it by cell references (using the INDIRECT function) which is possible because all of the subordinate sheets employ the same layout, even though row counts can vary.
SO that's for starters. What exactly is the data that you want to pull from those other sheets? Is it summary data OR a complete dump of all the data from those possibly several hundred rows?
- Thank you. Yes, you make a fair point - I think going forward the workbook structure could be standardized. There would be some work to change all the existing sheets which is why there seemed to be some push back.
The data that is being pulled is a data dump which makes it easier since it does not need calculations.
The master sheets looks like this as example:
Col A Col B Col C Col D Col E
1 Job Name Customer Description QTY PO#
2 Stripes ABC XYZ DESC - 1002 30 F43265
What would the code look like to pull from individual sheets? Also within the code how do you tell excel which workbooks to open to pull the data from. The names can be standardized but which command would tell excel what to open since new workbooks may be created at any time depending on what jobs comes in or can you tell it to open and check all files in a folder?
Thanks!To the extent that VBA or macros may be needed, I will need to defer to folks who know how to write those. I've written that kind of code, but not for a couple decades now.....my own preference is to work with functions and formulas, even in pulling from other sheets and other workbooks.
In my case, I do so by making use of functions like FILTER and INDIRECT, the latter used within another formula to "build a name" of a workbook or separate sheet from which data are being pulled. But doing that means knowing in advance (so to speak) the names of those external workbooks, at least for the first time each one is used.
That leads me to another set of questions, the answers to which would be helpful for anybody here desiring to help you.
What is the bigger picture here? That is, without revealing any confidential information, can you describe what the full business process is and where these various spreadsheets fit in that process? Obviously you're getting data via spreadsheet from (I assume) customers. That data includes purchases, with quantities and product (?) descriptions, along with names of customers and jobs. But how often is this data generated; is it always new and on top of whatever had come from that same source the time before, or is it updating with some new but mostly old?
What will happen with the master sheet once the data from all of those subordinate ones--now amounting to tens of thousands of rows (potentially)!!? Does it drive production of each product? Do you actually need the details -- the aforementioned tens of thousands of rows -- or can summary data by product code suffice?
Etc.
I ask not to be difficult, but because that's what I'd be doing if we were sitting down face-to-face, ideally with a white board on which we could sketch all of this out.....the goal being to come up with a clean and workable full process, with the spreadsheet component designed with that full business process or context in mind.
- Thank you again for your input. You ask some good questions. The individual sheets come from different salespeople who are creating these spreadsheets. These are generated every time a job comes up . They can also be updated with respect to job changes e.g. quantities, ship dates. Therefore it can either update changes and add new or rewrite all with most recent info whichever is more efficient to code / run. The detail is requested for now as a start instead of summary data since it will be used by logistics dept to see which specific items are coming up. The thought in the first iteration is to keep all the data but there is the possibility of eliminating rows/items once actually shipped.
