Search results

Hello Maps, You are most welcome. Thanks for your feedback and appreciation, it really means a lot to me.

Hello Dave, First of all, I hope your wife is doing much better now. Please don’t worry about the delayed reply. Thanks for attaching a sample of your spreadsheet. Since you mentioned that you haven’t used programming for a long time, we can keep the solution as simple as possible. From your...

Hello Pankaj Kumar, You can achieve this with formulas so that both the month headers and the distributed quantities update automatically whenever the dates change. 1. Generate the Month Headers In cell F5, enter the following formula...

Hello Maps, I’m glad the formula is working correctly in Office 365. Since the LET function is not available in older versions of Excel, you can use the same calculation by replacing the variable names with their actual cell references...

Hello EmilXmulo, Thank you for your feedback! We’re glad you found the dynamic week setup and the SUMPRODUCT formula useful for handling supervisor-wise attendance reports. If you need any further modifications or want to make the report more dynamic, feel free to let us know.

Hello EmilXmulo, Yes. The received quantity table can contain multiple entries for the same PO#, including partial receipts entered on different dates. The same formula will still work for calculating the current Outstanding Qty. This part of the formula: SUMIF($G:$G,A9,$J:$J) adds all...

Hello Maps, The previous formula calculates the year-to-date rebate from the start of the tax year, so if an employee starts in the second month, it would incorrectly include the first month's rebate as well. The calculation should instead start from the later of: the tax-year start date, or...

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...