Forum Discussion

sajeevjvarma's avatar
sajeevjvarma
Copper Contributor
Jun 28, 2023

Help in preparing an Excel sheet for data management

I have work book, in which there are two sheets. Sheet1 consists of some data. Sheet2 concerned cells has to be filled from sheet1 based on the class selected from the drop down list.

Date wise sub totals of columns in sheet2 has to be automatically generated when a new date is entered in date column of sheet2.

Kindly help me in making this.

A sample sheet is attached.

Requirement:
Sub Totals of H to M column should be calculated automatically When a new Date is inserted in the Date column , When a new Date is inserted.
As shown in the sample sheet.

Excel
  • NikolinoDE's avatar
    NikolinoDE
    Jun 28, 2023

    sajeevjvarma 

    To prepare an Excel sheet for data management with the described requirements, you can follow these steps:

    Step 1: Set up the Drop-Down List

    1. In Sheet2, select the cell or cells where you want to create the drop-down list.
    2. Go to the Data tab in the Excel ribbon and click on Data Validation.
    3. In the Data Validation dialog box, select "List" from the Allow drop-down menu.
    4. In the Source field, enter the range of cells in Sheet1 that contains the class options for the drop-down list.
    5. Click OK to apply the data validation.

    Step 2: Fill Sheet2 Cells Based on Selected Class

    1. In Sheet2, select the cell where you want to fill the data based on the selected class.
    2. Go to the Formulas tab in the Excel ribbon and click on the Insert Function button.
    3. In the Insert Function dialog box, select the VLOOKUP function.
    4. In the Function Arguments dialog box, enter the following information:
      • Lookup_value: The cell in Sheet2 that contains the selected class from the drop-down list.
      • Table_array: The range of cells in Sheet1 that contains the class options and corresponding data.
      • Col_index_num: The column number in the table_array where the desired data is located.
      • Range_lookup: Enter FALSE to perform an exact match.
    5. Click OK to apply the VLOOKUP formula.

    Step 3: Generate Date-wise Subtotals

    1. In Sheet2, select the column where you want to enter the dates.
    2. Right-click on the column header and choose Format Cells.
    3. In the Format Cells dialog box, select the Date format that suits your needs and click OK.
    4. In the cell next to the first date entry, enter a formula to calculate the subtotal for that date. For example, if the data to be summed is in column B and the dates are in column A: =SUMIF($A$2:$A$100, A2, $B$2:$B$100) This formula sums the values in column B based on the matching date in column A.
    5. Drag the formula down to apply it to the rest of the date entries.

    Now, whenever you enter a new date in the date column of Sheet2, the subtotals for that date will be automatically generated.

    Note: Adjust the cell ranges and references in the formulas based on your actual data layout.

    The Steps and the text was created with the help of AI

     

    I hope this helps you in preparing your Excel sheet for data management.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 28, 2023

    sajeevjvarma 

    To prepare an Excel sheet for data management with the described requirements, you can follow these steps:

    Step 1: Set up the Drop-Down List

    1. In Sheet2, select the cell or cells where you want to create the drop-down list.
    2. Go to the Data tab in the Excel ribbon and click on Data Validation.
    3. In the Data Validation dialog box, select "List" from the Allow drop-down menu.
    4. In the Source field, enter the range of cells in Sheet1 that contains the class options for the drop-down list.
    5. Click OK to apply the data validation.

    Step 2: Fill Sheet2 Cells Based on Selected Class

    1. In Sheet2, select the cell where you want to fill the data based on the selected class.
    2. Go to the Formulas tab in the Excel ribbon and click on the Insert Function button.
    3. In the Insert Function dialog box, select the VLOOKUP function.
    4. In the Function Arguments dialog box, enter the following information:
      • Lookup_value: The cell in Sheet2 that contains the selected class from the drop-down list.
      • Table_array: The range of cells in Sheet1 that contains the class options and corresponding data.
      • Col_index_num: The column number in the table_array where the desired data is located.
      • Range_lookup: Enter FALSE to perform an exact match.
    5. Click OK to apply the VLOOKUP formula.

    Step 3: Generate Date-wise Subtotals

    1. In Sheet2, select the column where you want to enter the dates.
    2. Right-click on the column header and choose Format Cells.
    3. In the Format Cells dialog box, select the Date format that suits your needs and click OK.
    4. In the cell next to the first date entry, enter a formula to calculate the subtotal for that date. For example, if the data to be summed is in column B and the dates are in column A: =SUMIF($A$2:$A$100, A2, $B$2:$B$100) This formula sums the values in column B based on the matching date in column A.
    5. Drag the formula down to apply it to the rest of the date entries.

    Now, whenever you enter a new date in the date column of Sheet2, the subtotals for that date will be automatically generated.

    Note: Adjust the cell ranges and references in the formulas based on your actual data layout.

    The Steps and the text was created with the help of AI

     

    I hope this helps you in preparing your Excel sheet for data management.

Resources