I am trying to design an Excel solution with 3 worksheets. All Worksheets are formatted as Tables. Worksheet #1 gets data from a SQL Server so number of rows changes constantly. Worksheet #2 and #3 are essentially reading data from Worksheet #1 and run some calculations such as VLOOKUP, SUM, etc. How can I make sure that the lengths of WS #2 and #3 change automatically according to the number of rows that WS #1 pulls from SQL Server so that all relevant rows in #2 and #3 get updated right after SQL upload? Currently after each update, I shall go to Design Properties and Resize tables. I Hope this is clear enough.
If we speak about Excel Tables that doesn't matter where do you place them - in same or separate worksheets. Having one query which takes data from SQL, based on it you may generate couple of more queries (or more taking into account other supporting queries), each of them transforms data receiving by first query and lands result into the separate table. Something like this.