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.
Please upload a sample of your worksheet, you will get better and more concise help if we can see and manipulate the work you have.
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.
<Bump>
Does nobody have an answer for this?
Are you looking for something like this?
Thanks for the reply, but unfortunately that is not what I am looking for.
As stated in the question I am looking for way to expand/contract the table, not add a new query. This is because I want to see the formulae, not just the results.
I'm confused. Are you looking for Formula Text? A formula that will display the formula that was used in a cell? A query expands and contracts with the source table, I'm not sure what you mean about a table that expands and contracts with a query. I'm sorry, I thought I knew, but I guess I don't understand what you are looking for.
Hopefully I can explain it more clearly.
The results of the query are loaded into a table. The values in this table are used to calculate intermediate- and final results. The formulas to calculate these intermediate- and final results are in a separate table. Made with CTRL+T, so that when this table is resized (by the bottom-right handle) these formulas and any conditional formatting get copied with the resizing and it is easy for a pivot table and other formulas to reference these results. I want others to see the formulas used for ease of reviewing and auditing.
As the size of the table created by the query varies, I want to know if there is a way to resize the table containing the formulas after the query to load the data has been run so it matches the size of the query table. When the formulas table is made by a second query, only the results are visible.
I hope this helps in your understanding of what I am trying to accomplish.
