Forum Discussion
Norman_McIlwaine
Aug 18, 2023Brass Contributor
Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.
I am trying to add 5 additional columns to the function but can get only a $value: Function : =LET(r,B1:G8,m,6,a,TAKE(r,,1),b,INDEX(r,,2),c,INDEX(r,,3),d,INDEX(r,,4),e,INDEX(r,,5),f,INDEX(r,,...
- Sep 07, 2023
Thank you. Do I understand correctly that csv62719 is you source file. If to avoid Power Query we copy/paste data from it into the sheet in main file, here by formula create massaged range and based on it and month/year/monthid parameters create aggregated result. Workflow is correct?
SergeiBaklan
Oct 08, 2023MVP
With Power Query it's better to calculate it separately (Opening Balance query in attached file), result will be like
and Closing Balance is calculated as
=SUBTOTAL(109,[Amount]) + Opening_Balance[Opening Balance]
Norman_McIlwaine
Nov 27, 2023Brass Contributor
I have a small problem with not having the capabilities to refresh the data on a IPAD, it is imposable to change the month and get accurate results in the Excel output. I have tried to work around this and still run into the same problem, I have also requested this as an enhancement to Excel. However, it is not high on their priority list, and I can't get enough votes to get the fix to move it up on the list.
Is there any other way to get the results without having to do a refresh every time.?