Dynamically moving totals when using list functions/formulas that create spills/arrays

Posted 11 days ago by Michael Weaver, CPA

M
Michael Weaver, CPA

0 Votes

When using some of the list functions and associated GL functions, it would be helpful if there was an option to include totals at the bottom of the array that dynamically adjusts as it grows or shrinks. As a simple example, assume you are using the ACCOUNTSWITHHISTORY function in Cell A2 to return an array containing all accounts that have history within the financial periods defined in the function. In cell B2 you are using the ACCOUNTENDINGBALANCE function to populate the ending balance for all accounts returned in the array in Column A. It would be very helpful if there were a way to include a total at the bottom of the Array in Column B that dynamically moves up or down as the array grows or shrinks. As a workaround, we often put our totals at the top of the array, but this isn’t always desirable.


Is there an excel function or Velixo function that can accomplish this? I am this could be accomplished with VBA but am hoping to avoid that if possible. I’ve attached a screenshot of the example described above which also includes some screen markups in red illustrating how such a feature might work. Thanks

0 Votes


1 Comments

Damien Zwillinger

Damien Zwillinger posted 4 days ago Admin

Hi Michael


Thank you for sharing your idea! I think it is a good one.


I think we could support this scenario, yes and possibly even extend this concept to List functions, e.g. adding a "Total" value at the end of the account range (A2#).


Have you however considered using the LET, SUM, and VSTACK functions to achieve this today?


Here's a rough example (using a Sage connection but same concept with an Acumatica connection and account groups)


0 Votes

Login or Sign up to post a comment