Forum Discussion
Dynamic spill filtered based on other spill
Hello: I am trying to help my sales department come up with a dynamic sheet for rep group presentations. All data is stored in one Master Data table. I have Column A working as =SORT(UNIQUE(FIL...
Yes, you can modify the second dynamic array function based on the first dynamic array function so that they both spill down and over.
You can use the new dynamic array formulas feature in Excel to achieve this.
When you press Enter to confirm your formula, Excel will dynamically size the output range for you, and place the results into each cell within that range.
Here’s an example of how you can modify your second dynamic array function based on your first dynamic array function:
=TRANSPOSE(FILTER(MasterData[Product],(MasterData[AccountName]=A4)*(MasterData[Rep]=SelectRep)))
You can replace A4 with a reference to the first cell in your spill Column A dynamic array. This will make sure that your second dynamic array spills down and over based on your first dynamic array.
I hope this helps!