How do you return summarized dynamic array from Filter function

%3CLINGO-SUB%20id%3D%22lingo-sub-2573687%22%20slang%3D%22en-US%22%3EHow%20do%20you%20return%20summarized%20dynamic%20array%20from%20Filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2573687%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20Community%2C%3C%2FP%3E%3CP%3EI%20am%20working%20to%20improve%20an%20excel%20based%20financial%20model.%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20takes%20inputs%20from%20a%20large%20table%20with%20multiple%20filter%20criteria%20inputs%20from%20the%20user.%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%20the%20calculation%20is%20executed%20from%20a%20DataTable%20(going%20through%20multiple%20inputs%20at%20once).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20is%20an%20example%20of%20what%20my%20input%20looks%20like%20and%20what%20I%20am%20looking%20to%20produce.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22e6oc6nosmoc71.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297521iF9A974040310A13B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22e6oc6nosmoc71.png%22%20alt%3D%22Example%20Data%20Set%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EExample%20Data%20Set%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22d94a96v8noc71.png%22%20style%3D%22width%3A%20424px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297522iF7641E69CF32E699%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22d94a96v8noc71.png%22%20alt%3D%22User%20Inputs%20and%20Desired%20Results%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EUser%20Inputs%20and%20Desired%20Results%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EI%20am%20looking%20to%20improve%20my%20current%20formula%26nbsp%3B%26nbsp%3B%3CUL%3E%3CLI%3E%3CP%3EAdding%20up%205%20Filter%20functions%20(one%20filter%20formula%20for%20each%20outcome%20column)%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaving%20a%20formula%20for%20every%20year%20(filter%20for%20by%20particular%20year)%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERepeat%20this%20for%20every%20year%20and%20every%20row%20i%20need%20to%20export%20....%20(i%20have%20like%2030%20rows)%3C%2FP%3E%3CP%3Ethat%20is%205*60*30%20%3D%209000%20filter%20functions%20(1800%20excel%20formulas)%3C%2FP%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20am%20looking%20to%20see%20if%20there%20are%20ways%20to%20improve%20this%20by...%3C%2FP%3E%3COL%3E%3CLI%3EOne%20formula%20per%20row%20(instead%20of%20one%20per%20year%20-%20so%20it%20will%20need%20to%20be%20dynamic%20array)%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20can%20use%20a%20Pivot%20table%20(and%20using%20VBA%20solution%20to%20update%20filters%20based%20on%20user%20selection)%20but%20updating%20Pivot%20filters%20does%20not%20work%20with%20a%20DataTable.%26nbsp%3B%20I%20can't%20trigger%20VBA%20(that%20updates%20Pivot%20filter)%20every%20scenario%20that%20DataTable%20executes.%20I%20am%20open%20to%20fix%20that%20problem%3B%20it%20could%20be%20entirely%20possible%20that%20i%20am%20using%20event%20handlers%20incorrectly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20have%20Lambda%20yet.%20This%20feels%20far%20more%20doable%20with%20Lambda%20....%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2573687%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2574385%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20you%20return%20summarized%20dynamic%20array%20from%20Filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2574385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1109705%22%20target%3D%22_blank%22%3E%40papa_austin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20it%20possible%20to%20provide%20sample%20file%20with%20information%20as%20on%20screenshots%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2574820%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20you%20return%20summarized%20dynamic%20array%20from%20Filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2574820%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BSergei%2C%3CBR%20%2F%3EI%20just%20attached%20the%20sample%20file%20in%20the%20post.%3CBR%20%2F%3EIt's%20important%20to%20note%20that%20I%20have%20to%20calculate%20multiple-input%20combinations%20via%20DataTable.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2574944%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20you%20return%20summarized%20dynamic%20array%20from%20Filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2574944%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20have%20been%20trying%20to%20solve%20this%20by%20using%20a%20Pivot%20table%20unsuccessfully.%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3ESetup%20-%20There%20are%20two%20files%20involved%3B%20file%231)%20one%20with%20data%2C%20file%232)%20calculation%20workbook%3C%2FLI%3E%3CLI%3EFile%20%231%20has%20a%20master%20table%20of%20data%20and%20a%20Pivot%20Table%20with%20filters%3C%2FLI%3E%3CLI%3EFile%20%232%20has%20a%20DataTable%20that%20runs%20through%20a%20multiple%20possible%20filter%20combination%26nbsp%3B%3COL%3E%3CLI%3EFilter%20combinations%20are%20user%20inputs%20in%20cells%26nbsp%3B%3C%2FLI%3E%3C%2FOL%3E%3C%2FLI%3E%3CLI%3ELet's%20say%20my%20DataTable%20is%20set%20up%20to%20run%205%20different%20filter%20combinations.%3COL%3E%3CLI%3EWhen%20I%20calculate%20the%20DataTable%2C%20I%20would%20like%20to%20have%20the%20Pivot%20Table%20filters%20update%205%20times%20and%20result%20in%205%20different%20calculation%20values%3C%2FLI%3E%3CLI%3EI%20use%20Worksheet_calculate()%26nbsp%3B%20event%20handler%20to%20update%20the%20pilvot%20table%20filters%26nbsp%3B%3C%2FLI%3E%3CLI%3EBut%20I%20cannot%20get%20worksheet%20calculate()%20to%20be%20triggered%205%20times%20when%20the%20DataTable%20is%20calculated.%26nbsp%3B%3C%2FLI%3E%3CLI%3EI%20know%20if%20I%20press%20F9%20five%20times%20(manually%20go%20through%20the%20DataTable)%2C%20it%20works%20just%20fine.%26nbsp%3B%3C%2FLI%3E%3C%2FOL%3E%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2575479%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20you%20return%20summarized%20dynamic%20array%20from%20Filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2575479%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1109705%22%20target%3D%22_blank%22%3E%40papa_austin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20I%20understood%20the%20question%20is%20not%20about%20PivotTable%20but%20how%20to%20do%20that%20with%20dynamic%20arrays.%20I%20missed%20with%20sample%20file%20-%20it's%20totally%20different%20from%20what%20is%20on%20screenshot.%20What%20is%20Data%20Table%20in%20it%2C%20how%20filter%20will%20be%20defined%20and%20what%20shall%20be%20desired%20result%20if%20we%20consider%20that%20file%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello Excel Community,

I am working to improve an excel based financial model. 

It takes inputs from a large table with multiple filter criteria inputs from the user. 

And then the calculation is executed from a DataTable (going through multiple inputs at once).

 

Below is an example of what my input looks like and what I am looking to produce.

Example Data SetExample Data SetUser Inputs and Desired ResultsUser Inputs and Desired Results

 

 

  • I am looking to improve my current formula  
    • Adding up 5 Filter functions (one filter formula for each outcome column)

    •  

      Having a formula for every year (filter for by particular year)

    •  

      Repeat this for every year and every row i need to export .... (i have like 30 rows)

      that is 5*60*30 = 9000 filter functions (1800 excel formulas)

I am looking to see if there are ways to improve this by...

  1. One formula per row (instead of one per year - so it will need to be dynamic array)

 

I know I can use a Pivot table (and using VBA solution to update filters based on user selection) but updating Pivot filters does not work with a DataTable.  I can't trigger VBA (that updates Pivot filter) every scenario that DataTable executes. I am open to fix that problem; it could be entirely possible that i am using event handlers incorrectly. 

 

I don't have Lambda yet. This feels far more doable with Lambda .... 

 

4 Replies

@papa_austin 

Is it possible to provide sample file with information as on screenshots ?

Hello @Sergei Baklan Sergei,
I just attached the sample file in the post.
It's important to note that I have to calculate multiple-input combinations via DataTable.

@Sergei Baklan 

So, I have been trying to solve this by using a Pivot table unsuccessfully. 

  1. Setup - There are two files involved; file#1) one with data, file#2) calculation workbook
  2. File #1 has a master table of data and a Pivot Table with filters
  3. File #2 has a DataTable that runs through a multiple possible filter combination 
    1. Filter combinations are user inputs in cells 
  4. Let's say my DataTable is set up to run 5 different filter combinations.
    1. When I calculate the DataTable, I would like to have the Pivot Table filters update 5 times and result in 5 different calculation values
    2. I use Worksheet_calculate()  event handler to update the pilvot table filters 
    3. But I cannot get worksheet calculate() to be triggered 5 times when the DataTable is calculated. 
    4. I know if I press F9 five times (manually go through the DataTable), it works just fine. 

@papa_austin 

As I understood the question is not about PivotTable but how to do that with dynamic arrays. I missed with sample file - it's totally different from what is on screenshot. What is Data Table in it, how filter will be defined and what shall be desired result if we consider that file?