User Profile
OliverScheurich
Gold Contributor
Joined Oct 04, 2021
User Widgets
Recent Discussions
Re: XLOOKUP not working for multiple criteria
It shouldn't matter that the tables are in different worksheets within the same workbook. If you move the cursor over e.g. GameStats[Score] as shown in the screenshot do you see the referenced values {61,57,69} as well in your wookbook and in my attached sample workbook? Please check if the cell containing the formula is formatted as text: Can you click in the green triangle of the headers in your dynamic table and check which message is shown?Re: XLOOKUP not working for multiple criteria
Your original function returns the intended result. My screenshot shows the translation of your original function for german Excel. If either of the tables isn't formatted as a dynamic table it doesn't work. Your screenshots show that you are working with dynamic tables. The left table in my screenshot and sample file must be named "GameStats". I assume your referenced dynamic table is named "GameStats". Then perhaps you've activated "Show formulas". Display or hide formulas - Microsoft SupportRe: Looking for help with a single Dynamic Formula Ranking Points by player and dropping lowest score
=REDUCE({"Name"."Finishes"."TotalPoints"},UNIQUE(Data[Name]), LAMBDA(u,v, VSTACK(u, HSTACK( v, TEXTJOIN(",",,SORT(FILTER(Data[Place],Data[Name]=v))), SUM(FILTER(Data[Points],Data[Name]=v)) ) ) ) ) A formula solution could be with LAMBDA and REDUCE. However I'd prefer a Power Query solution as shown in the attached file.Re: Data Formatting
An alternative could be Power Query which works in Excel versions starting from Excel 2010. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. Thanks a lot PeterBartholomew1 for providing the sample data in an Excel file and for the 365 solution.Re: Looking for help with a single Dynamic Formula Ranking Points by player and dropping lowest score
An alternative could be Power Query which works in Excel versions starting from Excel 2010. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.Re: Conditional Formatting or a Specific Filter Rule
An alternative could be Power Query if i understand what you want to achieve. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. I've only copied and pasted columns A to T from your sample file.Re: Student Behaviour Tracker
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. Since i'm not sure what you exactly want to achieve i've made up a database and a possible solution "By pupil name" only. If this does what you are looking for further reports should be possible. If this doesn't help then please disregard the suggestion.Re: Formual Error
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. Power Query M code: let Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Month"}, {"GetandTransform", (t)=> let sort = Table.Sort(t,{"Value", Order.Descending}), AddedIndex=Table.AddIndexColumn(sort,"Index",1), SelectRows = Table.SelectRows(AddedIndex, each [Index] < 4) in SelectRows }), #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "GetandTransform", {"City", "Value", "Index"}, {"City", "Value", "Index"}) in #"Expanded {0}"Re: Replacing a string of words with a single number
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. { { "Kunming", "14" }, { "Hongkong", "66" }, { "Suzhou", "8" }, { "Wuhan", "91" }, { "Dongguan", "4" } ,{ "Zhengzhou", "101" }, { "Harbin", "3" }, { "Hangzhou", "88" }, { "Qingdao", "79" }, { "Foshan", "140" }, { "Shenzhen Tianjin", "42" }} In the example in the screenshot we replace "Kunming" by "14" and "Shenzhen Tianjin" by "42" and so on. In the second sheet in the attached file is a formula that replaces strings with numbers.Re: Formula help
=LET(colsfromsheets, CHOOSECOLS(VSTACK(Tabelle1:Tabelle3!A3:J200),2,5,7,10), IFNA(VSTACK({"Non-Catalogue".""."Catalogue"."", "Product"."Number Required"."Product"."Number Required"}, HSTACK( FILTER(CHOOSECOLS(colsfromsheets,1,2),CHOOSECOLS(colsfromsheets,2)>0), FILTER(CHOOSECOLS(colsfromsheets,3,4),CHOOSECOLS(colsfromsheets,4)>0))),"")) Lorenzo has provided a solution for Microsoft 365 in the meantime. I'd have tried to achive the result with the formula above.Re: Formula help
=IFERROR(INDEX(Tabelle1!$B$3:$B$1000, SMALL(IF(ISNUMBER(Tabelle1!$E$3:$E$1000), ROW(Tabelle1!$E$3:$E$1000)-2),ROW(A1))),"") This formula works in my sample file in modern and legacy Excel for the non-catalogue products of sheet "Tabelle1". The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024. It returns the results from one sheet to the summary sheet. You can adapt these formula to all four sheets. The other formulas are in the attached file. If you have access to modern Excel and it's functions such as VSTACK, CHOOSECOLS, FILTER... then there's a possibility to return all results from the four sheets in one step.Re: Formula to retrieve data from several sheets and return values from given criteria.
=IF(ISERROR(VLOOKUP($A10,EY!C$3:C$33,1,FALSE)), IF(ISERROR(VLOOKUP($A10,MY!C$3:C$33,1,FALSE)), IF(ISERROR(VLOOKUP($A10,LY!C$3:C$33,1,FALSE)),"Not Found","LY"),"MY"),"EY") It doesn't return the NAME error if i use " instead of ” in my sample sheet. However i'm not sure what you exactly want to do.Re: AVERAGE reduced lambda on GROUPBY or BYROW formulas not working on specific workbook.
Unfortunately i don't know why this happens. However you can do many replacements in one step. With the sortcut Ctrl+F you can open the search and replace window (suchen und ersetzen in german Excel) and then replace for example ;SUMME by ;LAMBDA(x;SUMME(x)) In your scenario it should be ,PROMEDIOA by ,LAMBDA(x,PROMEDIOA(x)) In my sample file in german Excel i've made 23 changes in one step. Using ";SUMME" with ";" in the beginning should prevent unintended changes in other parts.
