Pull through info from individually named sheets

Question

If I want to create a master sheet which pulls data from individually named workbooks created by different users.

The master sheet will have columns for Cust name, Address, PO#, Quantity etc. 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. Also there at any time approx 75-100 individual workbooks (the naming of the workbooks can be formatted to be the same format) but the names of the workbooks can differ depending on what the customer's name and job name is but you don't know that ahead of time until the job comes in e.g. Customer Name-Job Name-Ship Schedule.

The user wants to master sheet to be created each time they want to run a macro and pull from the different sheets. The individual workbooks will each have approx. few hundred rows of data.

Is this something doable and that can be handled within excel and what is the best way to do it?

mathetes · Answer

David_C_M&nbsp;&nbsp;Definitely doable. BUT your description suggests that there could be ways to make the job a lot more reliable. &nbsp;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&nbsp;consistency in column organization, proper use of tables, etc.: why can't that be standardized?&nbsp;I have a master workbook that pulls data from subordinate sheets, but I do it by cell references (using the INDIRECT function)&nbsp;which is possible because all of&nbsp;the subordinate sheets employ the same layout, even though row counts can vary.&nbsp;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?

david_c_m · Answer

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!

mathetes · Answer

David_C_M&nbsp;&nbsp;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.&nbsp;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.&nbsp;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?&nbsp;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?&nbsp;Etc.&nbsp;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.

david_c_m · Answer

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.

mathetes · Answer

David_C_M&nbsp;&nbsp;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.&nbsp;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.

Forum Discussion

Pull through info from individually named sheets

5 Replies

Resources