Forum Discussion

Norman_McIlwaine's avatar
Norman_McIlwaine
Brass Contributor
Aug 18, 2023

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,,...
Excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 07, 2023

    Norman_McIlwaine 

    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's avatar
SergeiBaklan
MVP

Norman_McIlwaine 

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's avatar
Norman_McIlwaine
Brass Contributor
Nov 27, 2023

SergeiBaklan 

 

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.?

 

 

 

Resources