Forum Discussion
How to automatically expand table 2 with data from table 1?
I load a csv file to excel, which becomes table 1, in a sheet.
On another sheet i have table 2 that aggregates values of certain columns of table 1, using formulas.
When table 1 is loaded with new data, table 2 collects that information but doesn't expand or shrink it's rows based on that new data.
Is there a way for table 2 to dynamically change its size to match the input of table 1?
Or just to expand if there are more records in table 1.
Right now i have to manually expand table 2.
Thank you
4 Replies
Is there a way for table 2 to dynamically change its size to match the input of table 1?
No. Maybe with VBA...Possible alternatives:
#1 If you run Excel >/= 2016 on Windows this is achievable with Get & Transform aka Power Query:
- Get the CSV data with Get & Transform
- Perform the aggregations with Power Query
- Return the query result to a sheet, as Table2#2 If you run Excel 2021 or 365 this is probably doable with a dynamic array - this won't be a Table though
Hello Lorenzo ,
Happy New Year, by the way 🙂
Thank you for your reply.
I don't know what a dynamic array would do, but i wouldn't want to loose the capabilities that a table provides.
So I'm going to try asking help with VBA.
By the way, another issue i have, if you don't mind, is not being able to use Custom Sort in a table on columns that have formulas.
Excel sorts with one column but more than that, when it starts i can see the sorting happening but when it finishes it goes back to how it was before the sorting was applied. Which forced me to paste only values of that table on another table and then do the sorting.
Is this normal, or do i need to enable/disable some option somewhere?
I appreciate your help. Thank you.
Hi ricardo121 (Happy New Year too)
Re. the sorting issue. Depending on what your fomulas do and which cells/columns/rows they reference, it is possible that Excel revert the Sorting to ensure your formulas still work (I don't have a good example with me right now)
Get & Transform can do the sorting of the values on a separate sheet - no more need to copy/paste, just a refresh of the query once it is in place
