• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. A

    Workers bed-space allotment in Excel.

    Hi I need formulas to check the bed-space available and allot to the new workers in Excel. Attached excel sheet has A1 to A7, B1 to B7.... F1 to F7 are Rooms for Workers (Each Room has only 4 Bed-Space Available) C1-1 to C1-23 are Contractor1 has workers 23 Numbers C2-1 to C2-10 are...
  2. A

    Find the first value and display the adjacent cells values.

    Hi The below snapshot Column A to L is the base data. The right side P column is the Input and the Q column values are the expected results. Example Column C RM1 used in rows 8, 11, and 15 the product is 3, 6 10 Column J RM8 used in row 11 and 15 the product is 6 and 10 Likewise, I have...
  3. A

    Worksheet_Change procedure to generate few rows of data

    Dear Excel Genius I need the Worksheet_Change procedure to generate the production Batch Card from different products formulations. The below-linked thread was created and I got the procedure. But now I have some modifications in the source data as well as output structure too...
  4. A

    Missing value indication in SUMPRODUCT

    Hi As per the below image, I have used the SUMPRODUCT formula in the cells L3 to L13. In the Second row some of the RM not having the prices in the first row (D, E, I, and J) But, The formula sumproduct has given the results as usual. I need to get the indications if the Products having...
  5. A

    Production batch card with different part for one product.

    Hi I have created one batch card sheet for my regular productions as per the attached sheet Batch card old. it is working well until the products have only one part as a below-left screenshot. Now I have a product which is having 2, 3 and 4 parts of components in it. So I want to change the...
  6. A

    SUM by colour is not giving correct results

    Hi In the attached sheet sum by colour is not working or not giving proper results by using the below VBA Code Example in cells BS6 and BR33 sum value should be 100 but there is 99 Likewise many cells different values are there. Can anyone check and correct the code or guide me on what's the...
  7. A

    VBA date format not working

    As per below image, After running the code some of the cells date not formating as defined. How do I change the code to get "dd-mmm-yyyy" formats
  8. A

    VBA - Find the filter date for the subject line in email.

    Hi With the below code I am sending the email from outlook with the selected cells as an image. In the subject as per code, today date is showing. Instead of today's date, I want to show the selected date through a filter. Eg: If I filter 04-Feb-2020 then the subject should show 04-Feb-2020...
  9. A

    Find the RM(Raw Material) cost from different purchase quantity

    Hi I have RM1 (Raw Material) to RM10 (Raw Material) Purchase with different quantity and price. Also, Product1 to Product12 with different combination formulations of RM1 to RM10 as per attached sheet. I want to calculate Each product RM (Raw Material) cost per kg from the purchase price...
  10. A

    Find the purchase cost from consumption cost

    Dear All Find the attached workbook having the sheets of RM Consumption and RM Purchase. RM's are RM1 to RM51 In RM Consumption sheet I have RM Price and RM quantity, Whenever the RM Price changes I have highlighted by yellow colour. I need the price in RM Purchase sheet, next to the...
  11. A

    VBA - Document particular cells value, address and heading to New Sheet

    Dear All Find the attached sheet, In that, I want to find the yellow-coloured cells and it must be documented to the new sheet with its value and cell address. For that, I tried below code and I got error 438 Option Explicit Sub SelectColoredCells() Dim Sh As Worksheet Dim rCell As...
  12. A

    Code for sum the value with two criteria

    Hi The below linked excel file Module1, I have a below code to find out the Yellow highlighted cells without any values in that, After finding those cells I need to check each highlighted cells intersect column "A" Value (Date) and then it has to check those dates in another sheet then...
  13. A

    VBA code for search, find and pick the appropriate data

    Hi In the attached excel file have 3 worksheets called Permanent Formulas and the Temporary Formulas which are the source data and Master Data which is input data. In the Sheet Master Data Range A9: B12 is the input data. With this input data, it has to find the appropriate formulas from both...
  14. A

    Find the lesser closest date by given date and then multiply the intersect values.

    Hi I need a VBA code to get the below results. In the attached workbook Sheet1, Cell D9 is the given date and the cell B12: H12 is the data range Sheet2 A2: A15 is the list of dates where we need to lookup by VBA code In the list of dates in Sheet2, none is matching Sheet1, D9 dates of...
  15. A

    How to get data between two dates in vba

    Hi The below formula I tried to convert VBA code but I struck how I can tell VBA that between two dates and only in sales data to be extracted. IF(P1=EN2,(SUMPRODUCT((J8:J30495<=EN1)*(J8:J30495>=EO1)*(G8:G30495="Sales")*CF8:CF30495)) Cell J8: J30495 is sales date Cell G8: G30495 is having...
  16. A

    Sort data by date

    Hi The attached sheet having Sheets of Formulation and Batch card with VBA code which is giving desired results from the sheet "Formulation" according to the C4 and F4 cells value in the sheet "Batch Card". In the cell E3, I have a Date and also in the sheet formulation Column A, I have a...
  17. A

    How to understand each line of VBA code

    Hi The attached sheet has a below VBA code to calculate the expected results. Here I want to understand each line of the code what it does and where it is looking the data. Because I want to rewrite the code according to my other requirements which are the similar worksheet or nearby same...
  18. A

    VB Code required for one cell with multiple results

    In the attached sheet, I got multiple results in the sheet COA and cell B8, B10 and B16. To get the results I have a helper column in the sheet Master Data Column M, N and O. How can I avoid those helper columns to get the results in the sheet COA and cell B8, B10 and B16 as like now...
  19. A

    How to find what formula uses more memory in excel

    Hi I have Excel workbook with a memory of 8MB which is having normal formulation, array formulation and macro codes. I saved the same file in binary format then it is reduced to 5MB. I want to determine which formula is taking more memory or what are all taking memories in the sheet. Can...
  20. A

    Find highest value between given range

    Hi I have numbers from 1 to 100, 1000 to 3000, and 4000 to 5000 in column B. I need the highest value from the number 1000 to 3000 in cell C5. Expected answer in cell C5 is 2999. Please find the attached sheet. Can anyone help me out? Thanks in Advance.
  21. A

    Run recorded macro only in recent modified rows

    Hi In the attached excel sheet, I have recorded the macro to run the calculation steps as below. Macro Key is Ctrl + g Step 1 - Copy formulas from S2 to DK2 Step 2 - Paste it to S8 to DK32 Step 3 - click F9 (Manual Calculation) Step 4 - Copy S8 to DK32 Step 5 - Paste values in S8 to DK32...
  22. A

    How to reduce heap of formulas in a sheet

    Dear Excel Ninjas Please find the attached sheet with the formulation which I'm using to maintain our company details since 2015 to till date. The formulas from S4 to DK4 and it is continued up to S9600 to DK 9600. This excel sheet is getting stuck frequently with these heap of formulas. Is...
  23. A

    Multiple Numbers in one cell

    Hi In the attached sheet, cell A10 to A19 I have a value of 10080 to 10089 and the cell A20 I would like to add 10090&10091 and the cell A21 10092&10093&10094 In cell A9 I have used =MAX(A10:A20). My desired result is 10094. It means I want to add more than one value in a single cell also it...
  24. A

    Pivot table calculation field results

    hi Please find the attached copy of excel sheet with pivot table data which is showing two different % of revenue from the same data source and value. Can you check How it's calculated the both value and guide me to get the correct answer?
  25. A

    Subtotal, Index & match

    Hi As per attached sheet cell Q3 i have entered below formula and i got a result is 174 instead of 334. This wrong value due to Column F sales date is not in A-Z order. =SUBTOTAL(109,S11:INDEX(S11:S2000,MATCH(G2,F11:F2000,1))) Please do the needful to get the right answer. Thanks in Advance!