Dynamic SUM function & filtering-CodePudding

I have a Power Query table which updates on a regular basis. Table has columns named "Volume Third Party_some number". After update the number of the column with this name may increase or decrease. At the end of the table there is a calculated column which has a formula to substract from the last column with the name sum of the columns with the name written above.

In the power query editor I couldn't find any formula which would help me to get the last column with the name, so instead I'm trying to create dynamic SUM function/sub procedure which would get the last column with name "Volume Third Party_some number".

I have attached a screenshot

and from it you can see there is a column "Volume Third Party_47" and column "Volume Third Party_48". After update "Volume Third Party_48" was added but the SUM function is refering to the values from the column "Volume Third Party_47". So instead I need a function/sub procedure in VBA which would change reference to the last column in the formula, in this case to the column "Volume Third Party_48" and also put a filter to a new reference column (it should remove 0 & blank values), while deleting it from another one.

Any ideas how to make this VBA code?

CodePudding user response：

This can be made possible using some change in excel formula.

If the name of you table is Table1 and the first column header of your table is firstColumn, then you need to replace [@[Volume_Third_Party_47]] in your formula with

OFFSET([@[firstColumn],,COUNTA(Table1[#Headers])-2)

But you entire formula should be something like this:

=IFERROR(
   OFFSET([@Column1];;COUNTA(SAPCrosstab_UA[#Headers])-3)-
   SUM(
      OFFSET([@[Volume Third Party]];;;;
                (COUNTA(SAPCrosstab_UA[#Headers])-1) - 
                 MATCH("Volume Third Party",SAPCrosstab_UA[#Headers],0)
            )
      );
"")