Recent content by shamimarita

Hello wiferetire, Thanks for your kind feedback! Yes, the formula will automatically prevent the Outstanding Qty from becoming negative even when the total received quantity is greater than the total ordered quantity for the same PO#. The outer MAX(0,...) ensures that the minimum Outstanding...

Hello Richard03, Yes, exactly. The formula assumes that the received quantity is allocated to duplicate PO# rows from top to bottom, based on their order in the table. So, for ordered quantities of 103, 105, and 103, if 103 units are received, the first row will be fully covered and show an...

Hello Wandy, You can combine Annual Check and Monthly Check using the OR function, since both use the same conditions for the date in D2. =IF(D2="","",IF(OR(C2="annual check",C2="monthly check"),IF(D2<TODAY(),"Expired",IF(D2<=TODAY()+7,"Expiring Soon","OK")),"")) This formula will return...

Hi snowbunny99, You can handle the duplicate PO# rows by allocating the received quantity to each ordered row sequentially instead of applying the full received quantity to every matching row. For example, if your first data row is row 9, enter this formula in D9 and copy it down...

Hello bevtyler, Yes, you can simplify this quite a bit instead of continuing with multiple nested IF statements. If the criteria are in A2 and the percentages to test are in E2, you can use the following formula in I2 and copy it across: =LET( x,E2*100, c,A2, t,TRIM(SUBSTITUTE(c&"","%",""))...

Hello Almadolam, You can place the column-unhiding commands directly inside the password-protected macro. Try the following code: Sub INTEGITUS_HQ_FORMAT_VIEW() Dim userInput As String Const correctPassword As String = "345" userInput = InputBox( _ "Enter the security...

Hello PomDave, Let us know your feedback and solution.

Hello Marktuan, In Excel 365, a simple and efficient way is to combine XLOOKUP with MAXIFS. Assuming your data is in A2 (ID, Date, Value) and the lookup ID is in E2, use: =XLOOKUP(MAXIFS(B:B,A:A,E2),B:B,C:C) Or, to make sure it returns the latest value for the specific ID only: =XLOOKUP(...

Hello AprilLynnRN, Thank you so much for the kind words. Glad to hear it worked! The spill error is expected here. Excel Tables do not support spilled array formulas inside the table body. A formula like FILTER, UNIQUE, SORT, or TRANSPOSE(FILTER(...)) needs open worksheet cells to spill into...

Hello AprilLynnRN, Since you're using Excel 365, I think you're actually very close to a formula-only solution and may not need Power Query at all. If your source data is in an Excel Table named ScheduleTbl with columns Dates and Event, you can first create a list of unique dates...

Hello PomDave, Yes, this is possible with a small VBA user-defined function. Excel formulas cannot directly count cells by font color, so a macro/UDF is the best option. Please try this VBA function: Function CountTextByFontColor(rng As Range, colorCell As Range) As Long Dim cell As Range...

Hello Maps, Glad to hear that the issue is resolved now. You are most welcome.

Hello Maps, Try this formula there: =MIN(D17*5%,215*((YEAR($C$23)-YEAR($C$31))*12+MONTH($C$23)-MONTH($C$31)+1)) For your spreadsheet, it calculates: YTD Gross Salary = 68,515.71 5% of YTD Gross = 3,425.79 July to April = 10 tax periods Maximum deductible = 215 × 10 = 2,150 So the YTD tax...

Hello TrevorWestbrook, Thanks for your feedback. Keep contributing on the ExcelDemy forum to help each other.

Hello Maps, It is possible to show both: the rebate applicable for the current month (Column C), and the year-to-date rebate value (Column D). For the monthly rebate (April 2026 in Column C), use: =IF(DATEDIF(C21,C23,"Y")<60,8200,IF(DATEDIF(C21,C23,"Y")<75,10900,12100))/365*C22 Where: C21 =...