The following Power Tip comes to us from Scott’s Add-in user and Excel guru, David Clements. David provides insights on how to leverage the =SCOTT.GLMULTI function and Excel. Thank you, David!
The new =SCOTT.GLMULTI function allows multiple account inputs. While this method of selecting individual cells or ranges may be appealing to some, it is not the most efficient. Visually, it can be difficult to identify which accounts are included in each group, without going back to examine each individual formula. This can lead to common user errors such as referencing the same account in more than one group, or missing accounts altogether.
A far more effective approach is to leverage the power of Excel’s dynamic array formulas to specify account codes for each group. As it turns out, the Accounts parameters will also accept the results of an array formula, provided as a single argument. There are two functions in Excel 365 that come to mind for this task (although there may be more): the FILTER function and the TEXTSPLIT function. Depending on your specific needs or preferences, either one can be used with SCOTT.GLMULTI to help create simplified reports in Excel.
Templates for this approach can be found on the Scott’s template page.
Read the full post on David’s blog.