Generating Tables from List of Entries
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:
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.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.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.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!
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: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.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.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.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! Read More