Help with a Formula
Below is a formula I created to return specified rows and columns for a sheet named Detail and in the case or column 49 detect if there are comma separated values and explode them into separate columns. The formula works when there Is not comma separated values and returns and error #VALUE! instead. Can someone help me with this formula? =LET( f, FILTER( Detail!$A:$BZ, (COUNTIF($B$16:$B$25, Detail!$BP:$BP) > 0) * (TRIM(SUBSTITUTE(Detail!$AW:$AW, CHAR(160), "")) <> "")), base, CHOOSECOLS(f, 1,3,4,5,68),split49,TRIM( TEXTSPLIT(SUBSTITUTE(CHOOSECOLS(f,49), CHAR(160), ""),",")), split53,TRIM(TEXTSPLIT(SUBSTITUTE(CHOOSECOLS(f,53), CHAR(160), ""),",")), SORT(UNIQUE(HSTACK(base, split49, split53)),1, TRUE))Chart from dynamic array challenge
Hi (Excel 365 v2512 b19530.20144 Current Channel / Windows 11 25H2) Charts are definitively not my thing. Spent hours on the following, searching, testing… to no available The below chart data range is a dynamic array (could be wrong for the desired chart) - named GRAPH_Series on sheet GRAPH_Ranges - similar to the array on the left side of the pic. The arrays dynamically resize according to the 'START Year' & 'TOPN Cat' variables - so far so good Setting the Chart data range as =GRAPH_Ranges!GRAPH_Series and changing nothing else, the chart updates as expected according to 'START Year' & 'TOPN Cat'. On the other hand if I change anything in the Select Data Source dialog box the chart won't update properly anymore The expected chart is the same as above with 2020, 2021...2025 (instead of 1, 2,...6) horizontally & D, F, B, A as legend Thanks & any question please let me know Lz.
Excel pads dynamic array output with #NV values
I have come across a couple of instances, where Excel 365 pads the output (of lambdas) or in this case of a specific formula within a lambda with #NV values (or errors if you like to treat them so.) The following snippet: = LAMBDA(assemblyName; LET( filteredParams; {"a";"b";"c"}; mappedValues; {1;2;3}; transposed; MTRANS(HSTAPELN(filteredParams; mappedValues)); return; WENN(filteredParams = ""; "NV"; transposed); return ))("fake") Gives me the following output: a b c 1 2 3 #NV #NV #NV At the same time, the following slightly different code (letting alone the output of WENN): = LAMBDA(assemblyName; LET( filteredParams; {"a";"b";"c"}; mappedValues; {1;2;3}; transposed; MTRANS(HSTAPELN(filteredParams; mappedValues)); return; WENN(filteredParams = ""; "NV"; transposed); transposed ))("fake") Spits out: a b c 1 2 3 This is not the only case, but is the simpliest, I can reconstruct this kind of error with. Do not mind the logic, it is an edited excerpt, just for illustration. P. S. It is German syntax. English would have TRANSPOSE, HSTACK, IF,... instead. Maybe a little difference in punctuation.
AVERAGE reduced lambda on GROUPBY or BYROW formulas not working on specific workbook.
Currently using excel with spanish formulas. When using GROUPBY formula, i can use SUM, PRODUCT, LAMBDA, however AVERAGE (PROMEDIO) won't work on a specific workbook which previously has a lot of GROUPBYs that work properly. When I update the previously working formula, it now returns NAME error and PROMEDIO turns into Promedio. Inserting the function looks like this: I have no custom name called Promedio. This same GROUPBY formula works in different workbooks. Help would be appreciated. Best regards
Use Ctrl-Shift-Z to redo actions
Does anybody know how to make it so I can use Ctrl-Shift-Z to perform the redo action in Excel? It was... doable in Word, but Excel is proving to be a much more difficult challenge. I've tried working with macros to get the action done, but I can't seem to figure out how to get it to do what I want at all. Any solution is more than welcome! It's pretty nuts that in 2022 you still can't edit keyboard shortcuts in Excel, so if I'm missing an obvious solution, please let me know!
Date entered is changed to a different date
This issue has just started as far as I can tell. On an existing worksheet with the column formated to "date" 01/01/2025 format - when I enter a date of 01/01/2025 as for an example and hit "enter", the date is changed to 10/29/70! If I enter a date of 01/19/2026 it is changed to 08/27/63! and so on,..... it changes the field every time. if I enter those same dates by physically entering 01 slash 19 slash 2026 slash it will stay as I typed it - and will look like 01/19/2026 (or 01 slash 01 slash 2025) My husband is able to create a spreadsheet on his laptop that works correctly. When he sent it to me, the date fields did what I have stated above. So there must be something corrupt on my laptop? or Excel? I am running on the latest build (to my knowledge) Anyone? I am at a loss.. When entering a lot of data and dates - this is a real PIA. Plus - it was working just fiine - not sure what has changed. Thank youWelcome to the Excel Community
The Excel Community is a place we've built for all of you. You can learn more about how to do something with Excel, discuss your work, and connect with experts that build and use the product. With over half a billion Excel customers, we want to engage with you in fundamentally different ways and the community is a starting point for that. Our community helps answer your product questions with responses from other knowledgeable community members. We love hearing feedback and feature requests from you which helps us build the best version of Excel ever. If you have found an outage or a bug please post at our Answers forum. We look forward to getting to know you! Sangeeta Mudnal & Olaf Hubel on behalf of the Excel Team
