November 25, 2022
Not sure if this is the correct sub-forum, but as it's got to do with queries and data connections I hope it's ok.
An FEA calculation program I am using is creating a lot of output in the form of CSV files. The output consists of nodal results for several cases. I have made a query to import and combine these files into one results table. The next step is to use the values in this table to calculate the final results. For this I am using several formulas (placed in a separate formula table), which are to be used for each line of values in the results table. Finally the results from the formulas must be summarised for each node. For this I am using more formulas in a summary table.
I would like to create a template from this workbook, so it can easily be re-used. For this, I would like to have the formulas table and summary table expand and contract with the results table for each refresh of the query. The formulas table has the same number of rows as the results table, while the summary table has fewer.
While searching I found some things about table connections, but I cannot figure out how to get this to work. I have several similar sheets like this which I like to make into templates, so any help on this would be very welcome.
February 13, 2021
November 25, 2022
Here is an example.
A bit of background information: This workbook is used to calculate the fatigue damage of a structure due to waves. The structure has a defined number of nodes, which changes when a different structure is analysed. There is a certain number of waves heights/periods and directions (load cases) which are applied to the structure, which also differ for each analysis. The columns have no names, because the column headers are different for each load case due to the different wave periods examined.
The worksheets FileData and Table 2-2 contain general information needed for the calculations.
The worksheet SLONG contains the query and one of the tables that needs to expand or contract depending on the number of rows imported with the query. The summary can be found on the worksheet Results Table. This table needs to expand or contract based on the number of nodes found in the query. I know it is possible to add some of the columns of the 2nd table using a query, but not all of them (as far as I know). So I rather keep it like this so others know what is calculated and how. Also I can add the NodeNumber and Location fields to the 2nd table so a Pivot table can be used to get most of the current summary table. Unfortunately I cannot get the lifetime column to work that way (as it uses a defined name), so that needs to contract/expand anyway.
As the number of nodes and load cases is different for each analysis, I would like to know if there is a way to link the tables so that they expand and contract with the query.