User Profile
Harun24HR
Bronze Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Re: How to make excel add or remove rows to fit data and prevent spill error?
You first question answer is, you can filter many rows and columns using FILTER() function like- =FILTER('Source Sheet'!B5:D500,'Source Sheet'!$A5:$A500=TRUE) I assume you need one single cell dynamic formula which will spill all outputs at a time, means you do not need manually adjust formula when you add rows in source sheet. To avoid #Spill error, you can use TAKE() function. Suppose you want to keep 3 records, then limit filter function output inside TAKE() function like- =TAKE(FILTER('Source Sheet'!B5:D500,'Source Sheet'!$A5:$A500=TRUE),3) You may also need VSTACK() to stack all results into one formula. Please share a sample file, attach to your post or share via OneDrive, Google-Dive or Dropbox service.Re: How to refresh excel data and remove previous data automatically
You can still do that by power query. Power query can configure to auto refresh in every n minutes where n can 5, 10, 30 or any number. You can also use FILTER() function. Better, share a sample file and show your desired outcome. Attach the file or share via OneDrive, Google-Drive, Dropbox or similar service.Spell Number in Bangladeshi/Indian Style By Excel Formula.
On web there is SpellNumber() VBA custom function which spell numbers to word in million, billion. We Bangladesh, India, Pakistan spell amounts like lakh, crore etc. Here is a formula to convert amounts to word in Bangladeshi/Indian style. In attached file there is also a generalize LAMBDA() named function SpellNumberBD(). =LET(x,A2,lr,TEXT(INT(x),"00000000000000"),cr,LEFT(lr,7),lc,RIGHT(lr,7),dec,ROUND(x-INT(x),2)*100,c,CHOOSECOLS, digits,{"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}, Tenths,{"Ten","Twenty","Thirty","Fourty","Fifty","Sixty","Seventy","Eighty","Ninety","Hundred"}, SPELL,LAMBDA(val,abr,IFERROR(IF(val<20,c(digits,val),c(Tenths,LEFT(val,1)) & IFERROR(" " & c(digits,RIGHT(val,1)),"")) & SWITCH(abr,6," Lakh ",5," Thousand ",3," Hundred ",1," Poisa Only.",""),"")), Taka,SPELL(--MID(cr,1,2),6) & SPELL(--MID(cr,3,2),5) & SPELL(--MID(cr,5,1),3) & SPELL(--MID(cr,6,2),"") & IF(--cr>0," Crore ","") & SPELL(--MID(lc,1,2),6) & SPELL(--MID(lc,3,2),5) & SPELL(--MID(lc,5,1),3) & SPELL(--MID(lc,6,2),""), Poisa,IF(dec<=0," Taka Only."," Taka And " & SPELL(dec,1)),TRIM(Taka & Poisa)) Alternate Download Link (Google Drive).Re: How to filter visible cells by formula after applying filter to a dataset.
Riny_van_Eekelen SUBTOTAL() is important here. After few research, I am able to make it as a generalize LAMBDA() formula. Here is lambda. FILTERVISIBLECELLS=LAMBDA(Arr,LET(CheckVisible,SUBTOTAL(103,OFFSET(Arr,ROW(Arr)-ROW(TAKE(Arr,1,1)),0,1)),FILTER(Arr,CheckVisible))) And use of the function. =FILTERVISIBLECELLS(A2:B11)How to filter visible cells by formula after applying filter to a dataset.
I have a dataset from A1:B11 (in real case many more). I apply a filter on dataset, for example filter data for Retailer1 and Retailer3. I want on a separate sheet to filter these visible cells using formula (not VBA). Preferably using a generic LAMBDA function like =FILTERVISIBLE(A2:B11). Any thought or idea to achieve it?
