Hello, First post, still learning excel. Running Excel 2024 on MacOS. My issue is I have two sheets, First sheet labeled "Calculator", Has a drop down list that pulls a list of products from a Price/Quantity table on the second sheet labeled "Transfers". Depending on what product is selected in the drop down list (B3), and what Quantity is entered in (G3) cells (E3, F3, and I3) populate with the data attached to that product. Everything works if the sheets are not protected. When I protect the sheets, the VLOOKUP and INDEX formulas located in (E3, F3, I3) are not dynamic and do not register a ..read more

Looking to find out what is wrong with my formula per the attachment. B2 is returning a "FALSE" error when it should be returning "YELLOW" based on the conditions being met for C2 and A2. Based on the Value in C2 the color names should return differently based on the scales to the right. Attached Files LVL Coloring.xlsx‎ (12.4 KB) Download ..read more

Hello everyone, I'm trying to generate a counter for each employee based on cell values on multiple sheets. I have attached a mocked-up file of what I'm looking for. Example of data is shown on the January sheet (I need the counters on the summary page to keep adding up based on the data inputted on each sheet - January, February, March and so on). A screen shot of the counter I would like to use it shown on the summary sheet. I would need a counter for each employee. Thank you. Employee Absence Report COPYCOPY.xlsx ..read more

I have the following formula: =CONT.SES($C$2:$C2;C2) ... =CONT.SES($C$2:$C1055;C1055) =CONT.SES($C$2:$C1056;C1056) But when I add new line on this table the last row changes from to new table size Row+1; example: Last line is: =CONT.SES($C$2:$C1055;C1055) when I start the row (1056) below automatically the old last row (1055) turns to =CONT.SES($C$2:$C1056;C1055) and the last one =CONT.SES($C$2:$C1056;C1056)... If I add another one, All three lines will change =CONT.SES($C$2:$C1057;C1055) =CONT.SES($C$2:$C1057;C1056) =CONT.SES($C$2:$C1057;C1057)... If I keep like this, it keeps changin ..read more

HI all Doing a golf match handicap sheet, using 90% of course handicap. Most of the calculations are correct, but I have one that isn't. =ROUND(G8*$H$7,0) G8 is unrounded course handicap, H7 is a cell that has 90% in it(this is in case I need to change %), I could put in .90, but I want it flexible as the sheet is locked. The number I'm having trouble with is 23. 23 x 90% is 20.7, excel is rounding down to 20, but it should be 21! I don't want to use roundup or rounddown. any thoughts ..read more

I building a sheet where I track BP data. I have the raw data in a standardized text format: tttt: Sys/Dia/HR: optional notes I'm trying to extract various parameters (Systolic, Diastolic, Heart rate) from said data into adjoining cells (don't care about the notes). I've had limited success with the first after hacking together formulas, but testing has encountered errors. For systolic, I've managed to get this far with data in A2: =MID(A2, SEARCH(": ", A2) + 2, SEARCH("/", A2, SEARCH("/", A2) + 1) - SEARCH("/", A2)) I'm attaching a demo file. Any help would be appreciated. -- Bad Idea Fair ..read more

Hi In the attached workbook is anyone able to automate as a pivot what I've tried to do and labelled "Incorrect" but have done not as pivot and labelled "Correct" to illustrate the right answer and what I'm trying to achieve. Background: A) 2 tables on "Source" worksheet with Employee ID & Period as the unique fields to join the 2 tables using power query B) "PowerQuery Table Merge" worksheet contains the combination merged power query table called merge1 with Table2 being a left outer join to Table 1 to bring into Table 1 some fields I need from Table2. C) Pivot table called INCORRECT i ..read more

Hello, I am trying to find a formula to put in cell S1 that would return the last non-blank cell data in a column when data is filtered for a particular account. I've tried the following formulas, but they don't work for filtered data: Code: =XLOOKUP(TRUE,S2:S100000<>"",S2:S100000,,,-1) Code: =INDEX(S:S,COUNTA(S:S)) Code: =LOOKUP(2,1/(S2:S100000<>""),S2:S100000) The actual worksheet can have 50,000 rows of data and column "S" will have blank cells with formulas in them too. The formula would need to work in Excel 2016. Any assistance would be greatly appreciated. Attached File ..read more

I have over 100 clients who prefer credit-load, and the Payment Frequency Options available are Annual and Monthly. I need to establish a notification system that alerts me 10 days before the scheduled payment date, and also provides reminders closer to the payment deadline. For annual payments, we offer 5 rates per year so if start 15 Apr 2024 first rate is 15 Apri 2024, seconf 15 Apr 2025 ,2026,2027 and 2028, and for monthly payments, we have 30 rates every months lie if start 15 April 2024, next rate is 15 May 2024 ,15 June 2024 so total 30 rates every months of year. Therefore, every month ..read more

Hello All, I am trying to populate a cell with a specific date by checking a given date range. The conditions are as follows: 1. In a column, given dates are from 1st to last date of any month (Ex: 01/04/2024 to 30/04/2024). If the given date is in between 2nd and 15th (including 15th) of the month then I want to populate a cell with 15th or next working of the same month. 2. In a column, given dates are from 1s to last date of any month (Ex: 01/04/2024 to 30/04/2024). If the given date is in between 16th and 1st (including 1st of next month) of the month then I want to populate a cell with ..read more