Microsoft GabrielMichaud and JoeMcDaid
Both - Pivot table and Charts allow use "dynamic arrays" as source
This will be done through block names allowing dynamic ranges
For charts there is small limitation - creation time those range names have to by nondynamic and quite small (for smoth line chart I created 3 point table first)
once created pivot/chart and redefined to use named range the chart and pivot automatically follow the reshapings by formula edit or by formula recalculation
For chart I reported in previous post
For pivot I did similar test
Sheet1
Nr / Name / Year / Result
=Sequence(20) / =Text(a2#;"dddd") / =Randarray(count(a2#;;2000;2020;true) / =Randarray(count(a2#))
form this "table" (actually range) I created pivot table for year in columns, name in rows select sum(result)
fine
when I try change pivot data source to =sheet1!a1:sheet1!d2# - excel changed it back to A1:D21
but I created name range Tabel := Sheet1!$A$1:Sheet1!$D$2#
then - when I change pivot table source to range Tabel - everighting works
Actually same technic I used pre-dynamics era but rather than #-ranges I used =OFFSET function with same limitation
You can't but dunamic/offset formula directly into datarange or into series but through Named ranges only
For Data Validation was other limitation I had to use INDIRECT as range calculation formula
