Aug 29 2022 02:08 PM
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?
Aug 29 2022 02:33 PM
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?
Aug 29 2022 03:30 PM
Aug 29 2022 07:20 PM
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.
Aug 30 2022 04:02 PM
Aug 30 2022 06:23 PM - edited Aug 30 2022 06:30 PM
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.