Generating Tables from List of Entries

[tcoppens2025]

I'm looking for some help finding a way to automatically generate tables from a list of entries with multiple data points. For context, we use Excel to create a bill of materials table which we then link to a table in an AutoCAD .dwg file for our design drawings. Right now I have a separate Excel file set up with a long list of all the parts we commonly use, with one row per part, and each column has data like catalog number, description, power rating, temperature rating, heat loss, etc. (some columns are formatted for text, some are for numbers). I'll copy over the manufacturer, part number, and description cells from the data file to the BOM file, then tell AutoCAD to link the relevant cells.

I want to expand the functionality of this table. What I imagine the high-level description to be is this:

1. User copies/pastes rows for all the parts used in a design from the data file into sheet 1 of the BOM file. The user enters the ID number and quantity for each part and marks specific devices that must be shown in a separate table for torque and temperature specs.
2. Excel takes each entry's ID number, quantity, catalog number, manufacturer, and description and puts those cells in a table on sheet 2. The table is linked to the AutoCAD BOM table.
3. Excel looks for all the rows in sheet 1 that have an entry for heat loss and puts their ID number, catalog number, and heat loss entries in a table on sheet 3. The values are summed to give the total heat loss, and the table is linked to another AutoCAD table.
4. Excel looks for all the rows in sheet 1 that have been marked for the torque and temperature ratings table, and puts their entries for ID number, part number, torque, and temperature into a table on sheet 4. The table is linked to a third AutoCAD table.

We're familiar with most basic functions in Excel, but not in the ways of indexing. Any advice on functions to use and how to use them would be appreciated. Thanks, all!

[#2 · Accepted solution]

@tcoppens2025 

To achieve the functionality you described in Excel, you will need to use a combination of formulas, such as VLOOKUP, INDEX-MATCH, and SUMIF, along with some basic VBA (Visual Basic for Applications) coding for automation. Here is a step-by-step guide on how to set up your Excel workbook to automatically generate tables from a list of entries:

1. Set up your data:
• Sheet 1: This is where users will paste rows for all the parts used in a design from the data file. Columns should include ID number, quantity, catalog number, manufacturer, description, heat loss, torque, temperature, and any other relevant data.
• Sheet 2: Table for basic BOM information (ID number, quantity, catalog number, manufacturer, description).
• Sheet 3: Table for heat loss information.
• Sheet 4: Table for torque and temperature ratings information.
2. Formula setup:
• In Sheet 2, use formulas like VLOOKUP or INDEX-MATCH to retrieve data from Sheet 1 based on the ID number.
• In Sheet 3, use SUMIF to sum up heat loss values from Sheet 1 based on specific criteria.
• In Sheet 4, use formulas similar to those in Sheet 2 to retrieve torque and temperature ratings information.
3. Automate the process with VBA:
• Write a VBA script that runs whenever data is pasted into Sheet 1.
• The script should identify the newly added rows, extract relevant information, and populate Sheets 2, 3, and 4 accordingly.
• You can use the Worksheet_Change event to trigger the VBA script whenever changes are made to Sheet 1.
4. Linking to AutoCAD:
• Once the tables in Sheets 2, 3, and 4 are populated, link them to the relevant AutoCAD tables.
• You can use Excel's "Copy Link" feature to copy the cell range and paste it into AutoCAD as a linked table.

Here is a simplified example of the VBA code to get you started:

Vba code is untested, please backup your file first.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim newRow As Range
    Dim lastRow As Long
    
    ' Check if changes were made in Sheet 1
    If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
        ' Find the last row with data in Sheet 1
        lastRow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row
        
        ' Loop through the new rows
        For Each newRow In Target.Rows
            ' Extract relevant information and populate Sheets 2, 3, and 4
            ' Example:
            ' Sheets("Sheet2").Cells(lastRow, 1).Value = newRow.Cells(1, 1).Value ' ID number
            ' Sheets("Sheet2").Cells(lastRow, 2).Value = newRow.Cells(1, 2).Value ' Quantity
            ' ...
            
            ' Increment lastRow for next iteration
            lastRow = lastRow + 1
        Next newRow
    End If
End Sub

NOTE: My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text,Steps and code were created with the help of AI. Maybe it will help you further in your project, if not please just ignore it.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

@tcoppens2025 

As variant you may use Power Query on all steps, starting from importing of data file to generating linked tables.

If you are on Excel 365 or 2021, dynamic arrays formulae could be used.

Details depends on which Excel platform/version you are, do you mean under tables structured Excel tables or ranges, etc.

It's better to discuss on small sample file which illustrates input and desired output.