The user who posted this knows their way around Excel. They have two raw-data tables that change size daily, a third table that needs to mirror those changes, and a stubborn refusal from the software to cooperate. They have tried named ranges, dynamic arrays, Power Query, the usual solutions Google offers, and found each one blocked by a real constraint: their end users need pivot tables and structured references. The problem is not a lack of skill. It is that Excel tables, for all their convenience, were designed for a world where data stays put.
This is the frustration that defines a gap in the tool itself. A table in Excel will expand when you paste more rows into it, but it will not shrink when rows disappear. That asymmetry is baked into the product's logic: tables grow to accommodate new data, but they assume the old data still matters. For someone refreshing CSV exports every day, that assumption breaks down completely. Table3, built with direct references and a lookup, has no way to know that yesterday's 200 rows should be today's 140. The user has to manually resize or rebuild it each time. That is not a workflow problem, it is a design limitation.
Our take is plain: a data tool that cannot adapt to changing row counts is not truly modern. It is an improvement on a paper ledger, but it is not a partner in the work. The user's search for a solution that avoids Power Query and dynamic arrays is not stubbornness, it is a signal that the features they need do not yet exist in the form they need them. Pivot tables and structured references are not optional niceties; they are requirements for real-world reporting. A tool that forces users to choose between those features and automatic resizing has left a fundamental need unmet.
Until that changes, the workaround is to treat Table3 as a living structure that must be rebuilt on each refresh, not maintained. Write a macro that clears it and re-pulls the data, or accept a manual step as the cost of using tables. But the real answer is that the software should not make users choose. Tables that adapt to your data should be the baseline, not a wish list item. The user who posted this knows exactly what they need. The industry should be listening.