** Help ** Need to filter same result but in different formulas dependent on Quarter
Hi HansVogelaar I need help modify my formula/logic (request below) Formula 1 =TEXTJOIN("; ", TRUE, FILTER( Catalogue!$B$3:$B$987 & IF((Catalogue!$L$3:$L$987 = E$1 & "+"), "Q1'25+", "") & IF(Concat!$B$1 = "XXX Group", IF(TRUE, IF(Catalogue!$M$3:$M$987 = "Y", " UK", "") & IF(Catalogue!$O$3:$O$987 = "Y", " IT", "") & IF(Catalogue!$Q$3:$Q$987 = "Y", " DE", ""), ""), ""), (Catalogue!$A$3:$A$987 = $D$1) * (Catalogue!$C$3:$C$987 = $D2) * IF(Concat!$B$2 = "POR", (Catalogue!$F$3:$F$987 = "POR"), IF(Concat!$B$2 = "POI", (Catalogue!$F$3:$F$987 = "POI"), IF(Concat!$B$2 = "Both", (Catalogue!$F$3:$F$987 = "POR") + (Catalogue!$F$3:$F$987 = "Both"), TRUE))) * IF(Concat!$B$1 = "Superset", ((Catalogue!$J$3:$J$987 = E$1) + (Catalogue!$J$3:$J$987 = E$1 & "+")) * (Catalogue!$I$3:$I$987 = "Y"), IF(Concat!$B$1 = "XXX Group", ((Catalogue!$L$3:$L$987 = E$1) + (Catalogue!$L$3:$L$987 = E$1 & "+")) * (Catalogue!$K$3:$K$987 = "Y"), IF(Concat!$B$1 = "XXX UK & ROI", ((Catalogue!$N$3:$N$987 = E$1) + (Catalogue!$N$3:$N$987 = E$1 & "+")) * (Catalogue!$M$3:$M$987 = "Y"), IF(Concat!$B$1 = "XXX IT", ((Catalogue!$P$3:$P$987 = E$1) + (Catalogue!$P$3:$P$987 = E$1 & "+")) * (Catalogue!$O$3:$O$987 = "Y"), IF(Concat!$B$1 = "XXX DACH", ((Catalogue!$R$3:$R$987 = E$1) + (Catalogue!$R$3:$R$987 = E$1 & "+")) * (Catalogue!$Q$3:$Q$987 = "Y"), IF(Concat!$B$1 = "XXX CHOPS", ((Catalogue!$T$3:$T$987 = E$1) + (Catalogue!$T$3:$T$987 = E$1 & "+")) * (Catalogue!$S$3:$S$987 = "Y"), IF(Concat!$B$1 = "Omux", ((Catalogue!$V$3:$V$987 = E$1) + (Catalogue!$V$3:$V$987 = E$1 & "+")) * (Catalogue!$U$3:$U$987 = "Y"), IF(Concat!$B$1 = "Letxof", ((Catalogue!$X$3:$X$987 = E$1) + (Catalogue!$X$3:$X$987 = E$1 & "+")) * (Catalogue!$W$3:$W$987 = "Y"), IF(Concat!$B$1 = "DT", ((Catalogue!$Z$3:$Z$987 = E$1) + (Catalogue!$Z$3:$Z$987 = E$1 & "+")) * (Catalogue!$Y$3:$Y$987 = "Y"), FALSE)))))))) * TRUE ), "None") ) Request There are two things that I'm expecting to see return 1.) B243 = Test - do not remove M243 = Y (UK) N243 = Q1'25 O243 = Y (IT) P243 = Q2'25 Id expect to see returned in formula 1 (E2) "Test - do not remove UK" as this formula is looking for Q1'25 values in formula 2 (F2) id expect to see "Test - do not remove IT" as this is looking for Q2'25 values 2.) B306 = Migrate Verdi & Beethoven O306 = Y (IT) P306 = Q2'25 Q306 = Y (DE) R306 = Q1'25 Id expect to see Migrate Verdi & Beethoven DE in formula 1 as this is a Q1'25 value and id expect to see Migrate Verdi & Beethoven IT in returned in formula 2 as this is a Q2'25 value This logic should only apply when B3 = XXX Group on "Concat Tab" Formula Tab - so essentially instead of seeing Test - Do not remove UK IT in E2 Id like to see Test- Do not remove UK as it in Q1'25 for UK and then id expect to see in F2 "Test - Do not remove IT" as its in Q2'25 for IT and the same for E6 I expect to see "Migrate Verdi & Beethoven DE" as its in Q1'25 and Migrate Verdi & Beethoven IT in F6 as its in Q2'25 Catalogue Tab Catalogue Tab
Filling values if conditions are met
I want to have excel use a lookup to fill up a column if below criterias are met (I will update these criterias in a separate sheet as a table): So for example, my spreadsheet would look like below: So if I key in "Wong" and "China", then under Equipment the formula would look up the criteria table and enter "20ST" as a value. If I key in "Brad" and "UK" instead, then "40OT" would be the value. Appreciate the help!
Excel Creating Sales Tracker Different Values
I am creating a Sales Tracker for my business and need the total sales amount to change depending on who I filter the table to show. For example, I have 2 employees, John and Ringo, when I filter the spreadsheet to see how many sales John has made, I would like the Total Sales amount to change to reflect just his sales. Is this possible? Alternatively, I have created a secondary table with Sales for whole company, John sales and Ringo Sales however I do not know how to find the sum of just John's sales or Just Ringo's sales without doing it by hand.
IF statement - Empty cell to be blank
Hi, I'm fairly basic with my excel knowledge. I have the below formula and table. The break column is a drop down box with selected times, I want the lunch column to return either a time of 12:15 or 13:15 depending on the selected break. A break time of 10 past the hour will have a lunch at 12:15 and a break time of 11 past the hour will have a lunch at 13:15. This formula kind of works however, I would like any unselected breaks (Cell B4) to be blank in the lunch column. I have tried "IFBlANK" and possible not using this correctly as I am continuously getting error messages. Any suggestions welcome. Thanks.Working with Arrays of Ranges
OK, so strictly speaking an array of ranges is an error in Excel right from the outset. However, I set up an array of tables contained within the named Range 'allocatedRange', here shown with a red outline. I then set up a function that would return the range corresponding to a country SelectDataλ = LAMBDA(country, LET( countries, FILTER(TAKE(allocatedRange, , 1), markers = "Country:"), recordNum, FILTER(SEQUENCE(ROWS(allocatedRange)), markers = "Country:"), recordEnd, VSTACK(DROP(recordNum - 1, 1), ROWS(allocatedRange)), rangeRows, recordEnd - recordNum, countryArrϑ, MAP( recordNum, rangeRows, LAMBDA(start, rows, LAMBDA(TRIMRANGE(TAKE(DROP(allocatedRange, start), rows)))) ), XLOOKUP(country, countries, countryArrϑ)() ) ) The start and row count of each table is determined by using the string "Country:" as a marker and differencing row numbers. A combination of TRIMRANGE/TAKE/DROP picks out each range in turn and assembles them into an array by turning the range references into thunks (TYPE=128). The function SelectDataλ is used to look up any specific country and return the corresponding range. To demonstrate that the function indeed returns ranges, the COUNTIFS function is used to count the number of cities within each country that have a population exceeding 100 000. = LET( countries, FILTER(TAKE(allocatedRange,,1), markers="Country:"), countLarge, MAP(countries, LAMBDA(country, COUNTIFS(TAKE(SelectDataλ(country),,-1), ">1000000") ) ), HSTACK(countries, countLarge) ) The point of this post is to introduce the idea of treating tables as elements of an array, so allowing further tables to be inserted or removed dynamically. TRIMRANGE ensures that each range is sized correctly and MAP is used to analyse each table in turn. Whilst Excel throws hissy fits at being asked to store arrays of arrays, arrays of ranges, or nested arrays, it is perfectly happy to store arrays of functions. When evaluated, each element of the function array is perfectly free to return an array or even a range. The effect is to permit Excel to process 'arrays of Tables' faultlessly.
SharePoint opens different versions of files in the desktop app and the browser
Hello, I am experiencing two problems with Excel files on SharePoint. I have File A, which is linked via formulas to File B (File A serves as an input for File B). I am using Excel 365. Problem 1: Sync and Loading Issues with File A When I open File A using the desktop app, Autosave is enabled, and I can see other users working in the file (their cursors are visible). However, I do not see the changes they make. When I open the same file in a browser, I do not see myself listed as working on the desktop app, nor do I see the changes I’ve made in the desktop app. Additionally, when I try to close the desktop app, I receive the error message shown in the screenshot, but the loading process never completes. If I cancel the loading process and reopen File A in the desktop app, it displays the version I had previously saved via the desktop app. Furthermore, if I download the file from SharePoint, it matches the browser version. When working on Files A and B simultaneously in the desktop app, changes made in File A correctly affect File B. Problem 2: File B is based on desktop version of File A When I open File B, it displays the input from the desktop version. However, the other user sees the input from the browser version of File A.
Multiple filter in a pivot table to show the best "x" values using slicers
I have a pivot table with the following fields Row Fields Level 1 Level 2 Empl code Empl name Value Field Ranking By slicers, I make filters for the fields Level 1 and Level 2. I need to get the best x (e.g. the top 10) from the filtered lists based on the value of the Ranking field. don't know if it can be done with slicer, although it would also be useful to group the ranking by brackets (for example 1-10, 11-20....) and create a slicer with these brackets to filter the list. Thnks in advance.
