Dynamic pivot Values in a static chart

Weser 0
Viewed 37

I want to enter amount values from a dynamic pivot table into a static table.

I have attached a list as an example. https://docs.google.com/spreadsheets/d/1MLgrWesrFg4mEJosfBqDgehOOVY4hPvrlEyQ01sM1qE/edit?usp=sharing

In the pivot table you can filter e.g. by years or months.

What I have managed to do is to filter it correctly by years. But with the entry in the correct month field I do not get further. Currently, when I select a month, it writes the data in the first fields and not in the correct month field. My current formula looks like this:

=IF($B$3=$B$23;B24;IF($B$3=$C$23;C24;IF($B$3=$D$23;D24;IF($B$3=$E$23;E24;0))))

What I want to achive is that the values are sorted in the correct month. Currently he pasts all into the first cells. Even If I select month like June,July, August. He posts it in January, February and March enter image description here

google-sheets
1 Answers

to reference pivot table you can just do:

={A23:F36}

enter image description here

dynamically:

=OFFSET(A23;;;COUNTA(A23:A); COUNTA(A23:23))

enter image description here

or:

=INDEX(VLOOKUP(A4:A16&"♦"&B3:F3; SPLIT(FLATTEN({SUBSTITUTE(
 TEXT(A24:A44&1; "mmmm"); 1; )&"♦"&B23:F23&"×"&B24:B44}); "×"); 2; ))

enter image description here

player00
Related