Generating Tables from List of Entries

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.