One of the issues we have had with pulling account turnover is running into spill errors when ACCOUNTSANDSUBACCOUNTSWITHHISTORY() or ACCOUNTSWITHHISTORY() is used with a total row at the bottom. If the array grows, a spill error results because it runs into the cell with the word ("Total"), and formulas won't carry down because they run into the =SUM() formulas in the total row. If it decreases, there's a bunch of empty rows.
The method we've used to get around this is to create the header array (for example: =HSTACK("Account","Subaccount",TRANSPOSE(ACCOUNTPERIODLIST(...)))), and the GL code array (simply the ACCOUNTSANDSUBACCOUNTSWITHHISTORY() formula) below it in a separate array. Then use the TOTABLE(COLLATE(HeaderArray,GLCodeArray)) to pull those two arrays together into a table.
Then inside the table, run the ACCOUNTTURNOVER() or ACCOUNTENDINGBALANCE() formulas, which should automatically carry the formulas down anytime the table expands (you may need to nest a TEXT formula withing ACCOUNTTURNOVER() for pulling the periods correctly).
Then in Table Design, add a Total Row to dynamically add the totals. The table can then be designed with whatever formatting customizations desired for improved visibility. A little extra time during setup, but saves the time later so that you don't have to constantly add/insert/delete a whole bunch of rows, and re-format and re-calculate everything. Also avoids the beast of a single, combined LET(), MAKEARRAY(), LAMDA(), HSTACK(), VSTACK() array formula.
If anyone has any other methods to avoid the spill errors, feel free to chime in.
Thank you Gabe! Awesome video and example! Not nesting the Index formula into ACCOUNTTURNOVER() is where I went off course. Being able to index the columns of a multi-column array helps a lot. Thank you so much! :)
Hi Gabe! I have tried having extra rows below the ACCOUNTSANDSUBACCOUNTSWITHHISTORY() array to avoid spilling. However, if I leave them blank, it results in me having to manually drag down ACCOUNTTURNOVER() formulas anytime a new account/subaccount combo is used. But if I try to solve it with extra rows full of =IFERROR(ACCOUNTTURNOVER(),0) formulas, it slows refresh to a crawl because of all the extra formulas sitting in the rows between the bottom of the array and my Total row. Both of those avoid spilling errors, but create other problems I try to avoid when creating my reports.
In short, I'm looking for those ACCOUNTTURNOVER() formulas to dynamically change with the ACCOUNTSANDSUBACCOUNTSWITHHISTORY() array without either creating a spill error with the labeled Total row, or having to manually adjust which rows have formulas, or take up extra bandwidth and slow the workbook down.
0 Votes
Gabriel Michaudposted
1 day ago
Have you considered leaving a reasonable number of blank rows in every section, then using the auto-hide zero rows feature of Velixo? i've used this succesfully in the past. Also check out this option that you can use to configure how multiple consecutive blank rows are handled.
For your sums, are you using the spill-range reference, like =SUM(C13#) to make sure the sum automatically adapts to the range?
It would be great if the spill feature also allowed you to have fully expandable multi-section layouts, but unfortunately it's not an option :(
0 Votes
One of the issues we have had with pulling account turnover is running into spill errors when ACCOUNTSANDSUBACCOUNTSWITHHISTORY() or ACCOUNTSWITHHISTORY() is used with a total row at the bottom. If the array grows, a spill error results because it runs into the cell with the word ("Total"), and formulas won't carry down because they run into the =SUM() formulas in the total row. If it decreases, there's a bunch of empty rows.
The method we've used to get around this is to create the header array (for example: =HSTACK("Account","Subaccount",TRANSPOSE(ACCOUNTPERIODLIST(...)))), and the GL code array (simply the ACCOUNTSANDSUBACCOUNTSWITHHISTORY() formula) below it in a separate array. Then use the TOTABLE(COLLATE(HeaderArray,GLCodeArray)) to pull those two arrays together into a table.
Then inside the table, run the ACCOUNTTURNOVER() or ACCOUNTENDINGBALANCE() formulas, which should automatically carry the formulas down anytime the table expands (you may need to nest a TEXT formula withing ACCOUNTTURNOVER() for pulling the periods correctly).
Then in Table Design, add a Total Row to dynamically add the totals. The table can then be designed with whatever formatting customizations desired for improved visibility. A little extra time during setup, but saves the time later so that you don't have to constantly add/insert/delete a whole bunch of rows, and re-format and re-calculate everything. Also avoids the beast of a single, combined LET(), MAKEARRAY(), LAMDA(), HSTACK(), VSTACK() array formula.
If anyone has any other methods to avoid the spill errors, feel free to chime in.
0 Votes
Gabriel Michaud posted 1 day ago Best Answer
Here's a quick video as well as a sample file demonstrating the solution I have in mind: https://www.loom.com/share/17a8d42c72164ec993a4058301daa349
Attachments (1)
Sample Dynamic TB.xlsx
21.7 KB
1 Votes
4 Comments
Jreams posted about 23 hours ago
Thank you Gabe! Awesome video and example! Not nesting the Index formula into ACCOUNTTURNOVER() is where I went off course. Being able to index the columns of a multi-column array helps a lot. Thank you so much! :)
1 Votes
Gabriel Michaud posted 1 day ago Answer
Here's a quick video as well as a sample file demonstrating the solution I have in mind: https://www.loom.com/share/17a8d42c72164ec993a4058301daa349
Attachments (1)
Sample Dynamic TB.xlsx
21.7 KB
1 Votes
Jreams posted 1 day ago
Hi Gabe! I have tried having extra rows below the ACCOUNTSANDSUBACCOUNTSWITHHISTORY() array to avoid spilling. However, if I leave them blank, it results in me having to manually drag down ACCOUNTTURNOVER() formulas anytime a new account/subaccount combo is used. But if I try to solve it with extra rows full of =IFERROR(ACCOUNTTURNOVER(),0) formulas, it slows refresh to a crawl because of all the extra formulas sitting in the rows between the bottom of the array and my Total row. Both of those avoid spilling errors, but create other problems I try to avoid when creating my reports.
In short, I'm looking for those ACCOUNTTURNOVER() formulas to dynamically change with the ACCOUNTSANDSUBACCOUNTSWITHHISTORY() array without either creating a spill error with the labeled Total row, or having to manually adjust which rows have formulas, or take up extra bandwidth and slow the workbook down.
0 Votes
Gabriel Michaud posted 1 day ago
Have you considered leaving a reasonable number of blank rows in every section, then using the auto-hide zero rows feature of Velixo? i've used this succesfully in the past. Also check out this option that you can use to configure how multiple consecutive blank rows are handled.
For your sums, are you using the spill-range reference, like =SUM(C13#) to make sure the sum automatically adapts to the range?
It would be great if the spill feature also allowed you to have fully expandable multi-section layouts, but unfortunately it's not an option :(
1 Votes
Login or Sign up to post a comment