Recent content by shamimarita

Hello garfieldandew and williamcorlin, Thanks both for your suggestions. The TOCOL approach is useful for converting the lookup area into a single column, but as williamcorlin pointed out, the main challenge is keeping the cost in column N associated with the correct original row. That is why...

Hello Ehunt256, Yes, you found the correct solution! The Run-time error 13: Type mismatch can occur because one or more cells in the Inventory range may contain a value that Trim() cannot handle directly, such as a number or another non-text value. Your revised line: If...

You are welcome, Maps. It's my pleasure to help you.

Hello JJK, Good morning, and thanks for the update. If the same BYROW approach works in another workbook but not with the SAP export, the issue is likely related to how the SAP data is stored rather than the BYROW function itself. SAP exports often contain values stored as text, extra spaces...

Hello Maps, Since you are using Office 365, we can use the complete LET formula rather than changing individual parts of the previous formula. Based on the cell references from our earlier discussion, use this formula for your YTD tax: =IF(C23<MAX(C31,C24),0, LET(...

Hello JJK, You can use XLOOKUP with BYROW to search for an Item ID across multiple columns (E to M) and return the corresponding cost from column N. Assuming your lookup value is in cell P2, try this formula: =XLOOKUP(TRUE,BYROW(E2:M1000,LAMBDA(r,OR(r=P2))),N2:N1000,"Not found") BYROW checks...

Hello Maps, I checked this again, taking into account that you are using an older version of Excel. The YTD rebate of 673.97 is correct. Since the employee started on 01/09/2026, the rebate should only be calculated from her engagement date: 8200 / 365 × 30 = 673.97 The reason the YTD tax is...

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