Auto-Size Tables Based on Feeder Tables' Inputs
Our take
So this issue is not as simple as it seems.
I do not want solutions for Power Query. This issue is for applying to a tool for a different end-user. We use O365 desktop software.
I have two tables, Table1 and Table2, for the purpose of pasting raw CSV data. Some days there are 150 rows, other days there are 200 or 140 rows... Row counts vary. Table1 can also have an unequal number of rows compared to Table2. When adding raw data, a table naturally expands for more rows, but does not do the opposite for fewer rows.
Table3 is the combined table, where I use direct references to pull in data via "=Table1[@Col1]", for as many columns as I need. Table2 data is then XLKP'd into Table3 via a primary key column in Table1 and Table3.
I am effectively creating a LEFT JOIN using Table1, but need Table3 to auto-size itself. The problem is that Table3 will NOT auto-expand the number of rows when there are more rows in Table1 than Table3 currently has.
I have tried named ranges instead of tables, but the user prefers pivot tables and the features of actual tables in Excel, so using ranges and dynamic arrays are not the solution this time. (I love DA's but end users need more flexibility).
So I am requesting solutions on how to get Table3 to auto-expand (and even auto-shrink but I do not believe that exists).
I have exhausted Google.. it keeps telling me to use PowerQuery and dynamic arrays. The LOL part is that tables HATE having dynamic arrays in them, so i think it's funny that google recommends it.
Also, I believe i remember solving this problem in the past, but it eludes me.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Power query and manual table next to itHi, I want to pull data verbatim from a spreadsheet my team uses and use data from it for my own purposes. The main goal for using power query is that the data updates on my spreadsheet. Mainly, if any new entries are added at the bottom. I also have some manual fields that I need to add that correspond with the power query data. I've added another table beside the power query data, and filtering it causes the data on both sides to adjust correctly. I'm mainly concerned that, if the entries are rearranged or sorted on the original sheet, that my tables will not align after a refresh. Also, if a refresh would break my table alignments at any point. Is my fear founded? Is there a way to combine the two features that I need into a single table? submitted by /u/Perspective-Guilty [link] [comments]
- How to ENLARGE a spreadsheet to fit a page?This is the exact question I have: https://www.reddit.com/r/excel/comments/1kyt0dv/how_to_enlarge_the_content_to_fit_the_page/ that's a year ago with no answer. Wonder if anyone knows a better solution other than manually trying the zoom and keep increasing it till it's too big for 1 page, then back off a little. Seems LOOOOWWWW tech. FYI - anyone saying to click the Fit sheet to one page, fit all columns or fit all rows, that works for BIG sheets. It only shrinks the page. https://preview.redd.it/3tsrk658lqzg1.png?width=263&format=png&auto=webp&s=d6754edbb7cc3eda4457bd4e1b86e9400a645e73 Seems for this decades? old program, there's still room for more features? Again, seems the only work around is to click on custom scaling options and manually try numbers over 100%?? https://preview.redd.it/qkji3uzhlqzg1.png?width=543&format=png&auto=webp&s=9ba8a5e9b462eed3560ad9972d8a56be741f7bc6 submitted by /u/Kangaloosh [link] [comments]
- Power query for a large datasetMy company uses a horrible format for its daily production sheets, but the data can be pulled through power query. I want to build a reporting tool for looking at any major trends that are currently missed. Ideally looking at part efficiency by machine type and some other descriptive data too like efficiency by shift manger etc. My problem is that even after cutting unnecessary columns and filtering unnecessary rows, it takes forever to load anything. ChatGPT isn’t all that helpful, I’d like some expert advice please! For info, rough number of rows of data is about 50,000 per year. I want to cover at least the last three years. Sheets are all saved into a folder by month, within a folder by year. submitted by /u/CanJesusSwimOnLand [link] [comments]
- How do I Maximize File EfficiencyI work with data sets that I typically look at forecast by year. Currently when I look at 2026 and 2027 it is rougly 1.4M lines of data. I have to put these in two separate data pulls and tables. Then I have six different customers included in this data. so I have to create 6 tabs with six diffrent pivot tables for them to look at. This has created a massive file that lags just to open, save or close so I really have two questions and am open to suggestions. Would it be better to store the data in one worksheet and then link a second worksheet that just has the pivot tables and separated look? If so how would I creat that link? Can you explain to me like I am 5 how I would use power query to combine the 26 and 27 table so that they could be in the same pivot table? Every column in both are identical. submitted by /u/dcal69 [link] [comments]