IFS Formula Help
Hello, I have been struggling with a formula and I'm hoping for some insight. I am using it to separate employees and their information to another worksheet based on which school they work for. Their school assignment is notated by either their school name column (K), school code column (AL), or both. This is the formula: =IFS(K2="SCHOOL NAME",XLOOKUP(B2,B:B,E2,""),AL2="SCHOOL CODE",XLOOKUP(B2,B:B,E2,"")) I am trying to use an IFS statement to pull data from one sheet to another if the value meets at least 1 of 2 conditions. The formula seems to stop at the first value, even if the second value is true. That is, if an employee doesn't have a certain school name in column K but has the corresponding school code in column AL, their information is not pulling to the new worksheet. How I would like the formula to work: -Jane Doe has "X Elementary" in column K, then her information from the XLOOKUP will populate into the cell. (The spreadsheet has a lot of information, and I only need a few columns to pull to the new worksheet.) -If not, the formula will continue to search in column AL for the school code. -If the specific school code is found, then her information from the XLOOKUP will populate into the cell. If nothing matches the criteria, then a blank value will be returned. Thank you in advance!
Compare or search one data table in another table in Excel
Hi, We have two data tables in Excel that have a foreign key (a common field with the same data type). We want to search for the existence of data from the first table in the second table and identify if there are any inconsistencies in this comparison of the two tables. It should also display the found data separately. What method do you suggest? tnx
Initializing TextBoxes within a UserForm
My spreadsheet contains a macro which utilizes two UserForms, each containing several TextBoxes. I want to initialize several TextBoxes in each Userform. One UserForm works and the other one does not, even though I have set the code up identically in each one. Here is what I have detected: When you step through the macro code (F8), when the first UserForm.Show statement is executed, the control skips to the first line of code for the UserForm and when the second UserForm.Show is executed, the control skips to the first TextBox on the Object. Here is a sample of the code I am using: Private Sub UserForm1() TextBox1.Value = "ABCDE" End Sub I have been writing macros like this for over 30 years, but this is the first time I have run into this situation. Any help here will be greatly appreciated. Al
(Apparently) Unpredictable crazy colors in Excel dark mode
Hi, At LAST, there is a so-called Dark Mode in Excel, thanks for our eyes! Unfortunately, if the developpers wanted to discourage us from using it, they probably wouln't have done it any other way! In short, I tend to use colors in my spreadsheets to highlight value, especially inconsistent or unwanted ones, obviously. Now, someone at Microsoft seems to have deemed it a decent idea to (apparently randomly) display a dirty brown when I ask for yellow, Except in the buttons backgrounds, which renders the text unreadable, to break the "automatic color" feature that should make the text readable whatever the background color, and not to provide any way to predict what will be the end color of a random pick. Is there any way to set Excel so the color I pick is the one that is displayed in Dark Mode? At worst, is there any way to get a formula that will tell me what values I need to enter so I get the color I really want in my cell? Thanks for any decent update to this really needed feature!Formula help
I have the following formula that partially works. It processes this formula and returns the 1st match based on the formula criteria. =IFERROR( LET( KeyTypes,FILTER('Key Log'!$F$4:$F$309,(TRIM('Key Log'!$A$4:$A$309)=TRIM($D$6))*(TRIM('Key Log'!$C$4:$C$309)=TRIM($K$15))), KeyNums,FILTER('Key Log'!$K$4:$K$309,(TRIM('Key Log'!$A$4:$A$309)=TRIM($D$6))*(TRIM('Key Log'!$C$4:$C$309)=TRIM($K$15))), IF(INDEX(KeyTypes,ROW(A1))="Hard Key","V"&INDEX(KeyNums,ROW(A1)),INDEX(KeyNums,ROW(A1))) ),"") This formula resides in cells A8:A18 which is designed to return a key number, whether the key be a swipe key or a hard key. Cells B8:B18 are for descriptions of the key identified in cells A8:A18. This formula is supposed to find the 1st match on the key log and then find any other matches and place them in cells A8:A18. There is another formula for defining the description based on criteria. I need this formula to search out all transactions on the key log that match the ID number on the Key Issue Form in cell D6 with the cells in column A on the ID number and to also match the room number from K15 on the Key Issue Form with the room number in column C on the key log. There is something missing from this formula that is not allowing the other transactions to be found and listed on the Key Issue Form. Can anyone help me complete this formula? This is the formula for B8:B18 =IF(A8="","", LET( FilteredRows,FILTER(SEQUENCE(ROWS('Key Log'!$A$4:$A$309)),('Key Log'!$A$4:$A$309=$D$6)*('Key Log'!$C$4:$C$309=$K$15)), RowNum,INDEX(FilteredRows,ROW(A1)), Room,INDEX('Key Log'!$C$4:$C$309,RowNum), Status,INDEX('Key Log'!$E$4:$E$309,RowNum), Type,INDEX('Key Log'!$F$4:$F$309,RowNum), DateVal,INDEX('Key Log'!$D$4:$D$309,RowNum), IsAB,OR(RIGHT(Room,1)="A",RIGHT(Room,1)="B"), DoorDesc,IF(IsAB,"Combined Main & Room Door","Main Door"), KeyDesc,IF(Type="Swipe Key","Swipe Key","Hard Key"), DoorDesc & " " Can someone help me figure this out? Carl
Multiple greyed out buttons
I have a spreadsheet that is stored on a Teams page that multiple people can access. I used to have full functionality but now a lot of the buttons are greyed out. On the Review tab, it won't let me select Unprotect Sheet as shown in the attachment. All of the toolbars, Home, Insert, Page Layout, etc. have greyed out buttons. All of the sheets are ungrouped so that doesn't seem to be the issue. Does anyone have any ideas on how to get the functionality back?Dependent calculations in tables
Hi all, I'm using a formula to calculate amounts into a master column in budget sheets depending on the currency. I'm pulling currency rates with the stock function into a separate sheet with named cells for each rate that I'm using. My budget table has a "Total" column where I put in the amount, a "Cur" column where I select the currency, and then the master column where it's converted into the currency that I'm working with for each budget. The formula I'm currently using is this: =IFS([@Cur]="EUR";[@TOTAL];[@Cur]="NOK";nokeur*[@TOTAL];[@Cur]="SEK";sekeur*[@TOTAL];[@Cur]="GBP";gbpeur*[@TOTAL];[@Cur]="";) nokeur, sekeur, gbpeur are named cells containing the currency rates. The last argument is only to not get a REF error on empty rows. (Semicolons instead of commas due to my language settings) This has gone through a bunch of revisions over the years and I'm constantly trying to shorten the formula as much as possible. This is the shortest I've managed to get it though. I wanted to check if anyone has any advise on how to make it more elegant. the formula does exactly what I want so it's not a problem per se, it's more out of interest. Also a related topic: sometimes when others are using my sheets, they will mistakenly put values in the Master column, overwriting the formula. This is hard to spot, and ideally I'd like to lock those cells/that column so you can't replace what's there. Locking the workbook can of course achieve this but it also prevents the user from adding additional lines in the table. Is there any smart way to get around this? I've also tried with conditional formatting to clearly highlight the cells if they don't contain a formula but I haven't managed to make it work properly.
Controlling Formula Output in Excel Based on Cell Entry
Scenario Description In this scenario, cell B1 in Excel contains a value of 50. Cell B2 uses the formula =B1+25-A2 to calculate its value. When the number 25 is entered into cell A2, the result in B2 is 50. Desired Outcome The objective is to ensure that cell B2 remains empty until a value is entered into cell A2. Currently, B2 will display a result regardless of whether A2 has an entry. The goal is to prevent B2 from showing any value or calculation until A2 contains data. Question How can this behaviour be achieved in Excel so that B2 only displays a result after an entry is made in A2?
