Forum Discussion
Pull through info from individually named sheets
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.
My question about detail pertained to the master sheet: does that master sheet need to get full data dumps of all the detail from those individual sheets. I would expect the individual sheets to have the details so that they could be accessed if needed. The question is whether you always need all the detail at the central location, or would some summary or other be useful.
What I'm wondering--to be more specific--is whether a "dashboard" could be created that retrieved specific data, possibly customized to respond to various management inquiries (or production schedules, or whatever)....my assumption is that you're always going to want to process those potentially tens of thousands of rows in some way, to make the data manageable. Surely nobody is going to go through them one by one.....(or you'd be failing to take advantage of what the computer can do).... So that's what I'm getting at. What will you want to DO with all of that detailed info, recognizing the details are always available if needed, but what are you (management, your users) going to want to extract FROM the details? And then we'll deal with the question of how can we use Excel to facilitate those goals.