Search results

Hello Jznance, Thank you for your question! You can achieve this in Excel using a combination of COUNTIF or COUNTIFS formulas to track and count entries based on names across different sheets. Here’s how you can set it up: Master List (Sheet 1): Have a list of names in one column (e.g...

Hello Jogec, You can use the following formulas but must maintain a proper time format to get the correct result. Convert to Total Minutes: Once the time is correctly formatted, multiply it by 1440...

The additional levels (e.g., Moving Deviation, Reverse Deviation, and Midline) could be calculated using more specific formulas. Moving Deviation (High & Low): These levels might deviate from the pivot points using specific multipliers. High Moving Deviation: High Moving=High+(High−Low)×X...

Hello Akram Hassan, Based on your image, some extra levels like "Moving Deviation" and "Reverse Deviation" aren't standard Pivot Point calculations. These could be derived using variations of the High, Low, and Pivot Point values with some specific multipliers or averages. Possible Formulas...

Hello Akram Hassan, To create a program that calculates pivot points and related levels (R1, R2, S1, S2, etc.), you can implement this in Excel using formulas. Here's how you can approach both: Pivot Point Formula: Pivot Point (PP)=(High+Low+Close)/3 Resistance Levels: R1=(2×PP)−Low...

You are most welcome. Keep exploring Excel with ExcelDemy.

Hello Muh, I understand you're still facing issues getting your VBA code to run automatically upon reopening the workbook. Let's walk through the process step by step to ensure everything is set up correctly: Step 1: Open the VBA Editor Open your workbook. Press Alt + F11 to open the VBA...

Hello Muh, It sounds like the VBA code is not being triggered when you reopen the workbook. To ensure your code runs automatically when the workbook is opened, you need to place the code in the ThisWorkbook object in the VBA editor, specifically in the Workbook_Open event. Here's how you can do...

Hello Faisal, Unfortunately, Excel does not natively support dual-axis bar charts where each category (SAP, H2) can have a separate scale. However, here are a few workarounds you could try: Use a Combo Chart with Secondary Axis: Create a Combo Chart by selecting your data. Assign "SAP" to the...

Hello Faisal, Thank you for sharing your detailed requirements and the file. While I’m happy to provide suggestions and guidance, I encourage you to adapt the earlier solutions to your specific needs. This can be achieved using a formula that tracks the current date and adjusts calculations...

Hello Jululian, Here's the final solution using the working formula to extract the desired values based on two criteria: Formula: =SORT(UNIQUE(FILTER(C4:C33, ISNUMBER(MATCH(D4:D33&E4:E33, K4:K6&J4:J6, 0))))) Explanation: MATCH: Combines D4:D33 and E4:E33 to evaluate both criteria, comparing...

Hello Jkondrat14, Here’s a comprehensive guide to consolidating data from 300 individual sheets into a Master sheet in Excel: Option 1: Power Query (No Coding Needed) Load Data from SharePoint: Go to Data > Get Data > From File > From SharePoint Folder. Enter the SharePoint folder URL and...

Hello kajohnson9, The #VALUE! error typically occurs if the function tries to process invalid or incompatible data. Here are steps to resolve it: Verify Data in Column C: Ensure all cells in Column C contain valid dates that Excel recognizes. Non-date entries can cause errors. Error Handling...

Hello Muh, To calculate the aging report from Sheet 1, you can use the following formulas: Balance Amount: =SUMIFS(C2:C10, B2:B10, "DRAGON PVT LTD") To get the balance per customer, change the ranges for other customers. Aging buckets: For <30 Days: =SUMIFS(C2:C10, B2:B10, "DRAGON PVT LTD"...

Hello Faisal, To achieve a gradual daily total, you can use this modified formula, assuming your dates are in Column A and production in Column B: =IF(TEXT(A2,"dddd")="Sunday",B2, C1 + B2) Here’s how it works: Place this formula in Column C (starting from C2) and drag it down. For each week...

Hello kajohnson9, Your VBA function has a few issues causing the error: Undefined Variable: The variable ws is not initialized or set to any worksheet. Error in syear Assignment: ws.Range("F" & ActiveCell.Row) references ws, which isn't defined, leading to "Error 91." Update the function as...

Hello Era, Thank you for following up, and I'm glad you found a solution that works for you! A helper column is a great approach when working with complex conditional formatting, especially when you must alternate colors based on multiple conditions. Your method with the helper column (Column...

Hello, To search across multiple sheets with varying columns in Excel VBA, follow these steps: Loop Through Sheets: Use a For Each loop to go through all sheets where you need to search. Identify Columns: Check for column positions dynamically. If column positions vary, you can use Find or...

Hello Era, Thank you for your response and the screenshot—it’s very helpful. Let’s adjust the approach: New Formula: Try this formula for alternating colors based on unique combinations of Employee and Supervisor: =MOD(SUMPRODUCT(($E$2:$E2=$E2)*($D$2:$D2=$D2)), 2) = 0 This formula alternates...

Hello Era, You can use conditional formatting with a custom formula in Excel to accomplish this. Here’s how you can set it up: Select the cell range, including both the Employee and Supervisor columns. Go to Home >> Conditional Formatting >> select New Rule. Choose "Use a formula to determine...