•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Dynamically pull rows from multiple workbooks to combine on one workbook
Our take
Are you tired of juggling multiple workbooks and manually compiling data? Imagine seamlessly pulling rows from various sheets into a single master workbook, all while keeping track of totals effortlessly. By leveraging dynamic formulas, you can automate this process, ensuring that every new workbook added is included automatically. In this guide, we'll explore how to set up your master workbook to dynamically compile data from your 'Lead' sheets and generate a grand total with ease.
Hey all, I'm a bit stumped on what seems to me a simple thing to do.
Essentially, I have multiple workbooks that were created off of a template. As I enter a global name applied to each workbook, I add something at the end of the name to make each workbook unique. I need to be able to pull 4 sequential rows from a sheet, Lead, off of each workbook and compile them into a master list so to speak. Additionally, I'm trying to do this dynamically so when another workbook is added, it will add it to the compilation. Finally, each sequence of rows has a total amount on the last row; I'd like a way to be able to create a grand total from the totals of each workbook that was entered.
I have two sheets on the master workbook, Sheet1 and Lead. So far, I've managed to create a basic table on Sheet1 for testing with the following headers: Project Name; and, Project Number. Then I use TEXTAFTER(TEXTBEFORE(CELL("filename"),".xlsx]"),"[") to get the base Project Name, and have the user manually enter the number of related workbooks. So for example, I have 2 workbooks named "Book1 Test1" and "Book1 Test2"; the master workbook I'd be working in would just be called "Book1" and then the user would have to manually enter "Test1" and "Test2" in the "Project Number" header.
Afterwards, I have to dynamically map each Project Number to the Project Name which I'm having a little trouble with. What I've got right now is just =A2 & " " & B(2/3/4/...) & ".xlsx" which is a bit lacking. Lastly, I have =HSTACK(IF('[Book1 Test1.xlsm]Lead'!A22:A27="", "", '[Book1 Test1.xlsm]Lead'!A22:A27), '[Book1 Test1.xlsm]Lead'!D22:D27) which pulls the two relevant ranges that I'm trying to compile. I have to dynamically map these ones as well.
The end result compiles the two ranges, A22:A27 and D22:D27 from every workbook's 'Lead' sheet into the master workbook.
Some images for reference:
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How would I make a workbook where the first sheet VSTACKS data from all subsequent sheets and then sorts them by column?Basically, I have a workbook with separate sheets for separate clients, and each sheet has part numbers in it. I want one sheet to summarize all the part numbers in the subsequent sheets. Basically, the VSTACK function to add the following sheets. But where my design gets complicated is this: I want to be able to add a new sheet (basically, create a new customer) and have that new sheet dynamically included in the summary sheet. I.e., I don't want to re-write the VSTACK formula each time I add a new sheet because that will just end up with mistakes being made. Is there a way to dynamically capture ALL following sheets in VSTACK? And second, in the summary sheet, how can I sort the data by a criteria other than listing it in the order of the sheet it appears in? So in other words, I want to summary page to sort by part number, regardless of what customer's sheet it appears in. Thank you so much! submitted by /u/35usc271a [link] [comments]
- Pulling data out of multiple tables across sheets to a master table?I am seeking to make a workbook that tracks to do lists across multiple different projects at our firm. We have about 50 projects going, so I made a pivot table and created a sheet for each project. Next, I want each sheet to have a list of tasks and deadlines, my thinking is that I need to make a table on each sheet, and then find a way to pull each respective table’s task/deadlines to the master sheet. Each project has different number of entries, or none at all is there a good way to create a table on each sheet without having to do it individually on each sheet? which function do I use to pull the data from each table into the master table? thank you for your explanations & education in excel submitted by /u/randominternetguy3 [link] [comments]
- Workbook from Microsoft Form encountering very long load times from excessive complex formulasGood evening, I work in a food production plant in Shipping and Receiving. We have had Microsoft Forms for entering in daily cases produced, cases shipped, and a separate form for doing time studies on trucks that come in, how long to load or unload said truck, and when they leave. I have had a manual workbook to fill in all of this data basically again (this information gets entered into these daily reports we fill out in our Microsoft forms) but to organize it into an easy daily report to give us truck In to Out averages, loading time averages, cases produced vs what was scheduled to produce, etc.. A big issue I have had with this manual data entry workbook, which are done month by month, is the amount of formulas which I have in it..(multiplying cases by item number to give us weight and how many skids, calculating our scheduled amount to produce against what's actually produced, giving percentages, many conditional formatted cells to easily show if we are in the green or red, etc.) Now my boss has always wanted a workbook to do what my manual workbook does but to grab the data from the Excel workbook that these Microsoft forms load the data into. The problem before was we had two separate Microsoft forms for daily cases produced/shipped and the one for our time studies. But I went ahead and made one form which would do both. I was able to copy over many sheets and formulas from my manual workbook into the Excel spreadsheet that loads in the data from this Microsoft Form. My boss really wants it to work indefinitely.. The problem I am encountering which I was afraid of, is the amount of formulas in this one workbook is way too much for a computer to handle. Changing 1 thing results in it needing to calculate a thread for like 20-30 minutes (like with the manual excel spreadsheet, the manual processor has been set to 1). Am I just going about this all wrong? Is there a better way to grab the data from this form that isn't going to overload a computer? Do I make separate workbooks pulling from this form's Excel workbook and just keep the daily report with the initial Microsoft Form workbook (but then would those workbooks update automatically as well?) I imagine there is a way to achieve what my boss is wanting, but my experience with Excel is only so advanced. I'm aware there are other programs or other tools of excel, and that is why I came onto this subreddit for advice. Please help me 🙇🏻♂️ submitted by /u/maverickrose [link] [comments]
- Trouble coordinating auxiliary sheet in an online workbook to sheet w/ primary dataI have an online workbook that is used to record, monitor, and manage a full year's academic schedule for the college I work for. I have recently become the one in charge of this workbook, and I have spent many hours improving it and making it both more automated and more foolproof. This workbook has several sheets that, at times, reference each other. One sheet is basically the primary data set that shows the actual schedule with 20+ columns of details per row. Another sheet is there for the purposes of tracking and managing non-course-related releases and work that would reduce faculty workload. As such, this data is also listed on the primary sheet ("Master Schedule"). The problem I am having is this; on the auxiliary sheet that is used for tracking non-course-related work of the faculty ("Release Tracking"), all the columns in this table are auto generated based on the data on the Master Schedule, except for column I. Column I is where I manually select (from a data-validated list) a status (pending, accepted, denied, etc.) to assign to the release. The reason I want this data on Release Tracking instead of Master Schedule is because that the number of instances when the condition that triggers something to populate on Release Tracking is only about 3-5% of the total data on Master Schedule. Plus, Master Schedule is already super wide, so avoiding adding more columns to that table is highly preferable. If you haven't guessed already, my trouble is that when the Master Schedule is resorted or the row order of that table is otherwise changed, the status in I of Release Tracking do not move with the rows on that table. I did forget to mention that both Master Schedule and Release Tracking are both formatted as proper tables. I have a hidden helper column in the Master Schedule table that, when a particular condition is met, triggers a unique ID that another hidden helper column in the Release Tracking table can use to populate that table. That formula is: =IF(C12="N/A","MS_"&ROW(),"") The formula in the hidden helper column of Release Tracking is: =IFERROR(INDEX(master_schedule_table[Index ID],AGGREGATE(15,6,(ROW(master_schedule_table[ [ CRN] ])-ROW(INDEX(master_schedule_table[ [ CRN] ],1,1)) + 1)/(master_schedule_table[ [ CRN] ]="N/A"),ROWS($J$2:J2))),"") The formula in the A column of Release Tracking is: =IF(J2="","",IFERROR(INDEX(master_schedule_table, MATCH(J2, master_schedule_table[Index ID], 0), 12), "")) And the rest of the columns (B:H) follow this pattern except that they reference the appropriate correlative column on the Master Schedule. I have spent probably 15-20 hours trying to figure out a solution, but everything I try that allows me to keep the functionality and design of the workbook the way I want it, fails because, ultimately, the formulas on Release Tracking involve elements of relative positioning and because I cannot find a way to write the ID-generating formula in the helper column of the Master Schedule to create an ID with at least a static element and triggers the ID to come and go as the condition in column C of the Master Schedule comes and goes. To summarize what I want, I need a solution that 1) avoids the problem of the rows on Release Tracking changing when the Master Schedule's rows are changed or reordered (thereby linking my status selections to rows other than the one I want that status to be linked to), 2) allows me to manage/change/update the status on Release Tracking instead of Master Schedule, and 3) at least filter the rows on Release Tracking. Release Tracking doesn't necessarily have to be a full table, but for practical purposes, I need to at least be able to filter those rows. Is this possible with an online Excel workbook or am I just spinning my wheels and wasting my time trying to make this happen? submitted by /u/AndrewRyanPatrick [link] [comments]