Home

Dynamically change a table size to match the size of another table?

%3CLINGO-SUB%20id%3D%22lingo-sub-748857%22%20slang%3D%22en-US%22%3EDynamically%20change%20a%20table%20size%20to%20match%20the%20size%20of%20another%20table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-748857%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20design%20an%20Excel%20solution%20with%203%20worksheets.%20All%20Worksheets%20are%20formatted%20as%20Tables.%20Worksheet%20%231%20gets%20data%20from%20a%20SQL%20Server%20so%20number%20of%20rows%20changes%20constantly.%20Worksheet%20%232%20and%20%233%20are%20essentially%20reading%20data%20from%20Worksheet%20%231%20and%20run%20some%20calculations%20such%20as%20VLOOKUP%2C%20SUM%2C%20etc.%20How%20can%20I%20make%20sure%20that%20the%20lengths%20of%20WS%20%232%20and%20%233%20change%20automatically%20according%20to%20the%20number%20of%20rows%20that%20WS%20%231%20pulls%20from%20SQL%20Server%20so%20that%20all%20relevant%20rows%20in%20%232%20and%20%233%20get%20updated%20right%20after%20SQL%20upload%3F%20Currently%20after%20each%20update%2C%20I%20shall%20go%20to%20Design%20Properties%20and%20Resize%20tables.%20I%26nbsp%3B%20Hope%20this%20is%20clear%20enough.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-748857%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-748861%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20change%20a%20table%20size%20to%20match%20the%20size%20of%20another%20table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-748861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F70866%22%20target%3D%22_blank%22%3E%40Nima%20Mohandesan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20shift%20on%20Power%20Query%20to%20import%20data%20from%20SQL%20server%20and%20transform%20them%20into%20another%20two%20tables.%20When%20everything%20will%20be%20updated%20with%20each%20refresh.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-749033%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20change%20a%20table%20size%20to%20match%20the%20size%20of%20another%20table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-749033%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20could%20bring%20in%20the%20data%20via%20Power%20Query%2C%20but%20how%20does%20it%20help%20with%20those%20other%20two%20tables%20in%20Excel%3F%20They%20still%20need%20to%20be%20worksheets.%20Can%20you%20please%20elaborate%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-749035%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamically%20change%20a%20table%20size%20to%20match%20the%20size%20of%20another%20table%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-749035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F70866%22%20target%3D%22_blank%22%3E%40Nima%20Mohandesan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20we%20speak%20about%20Excel%20Tables%20that%20doesn't%20matter%20where%20do%20you%20place%20them%20-%20in%20same%20or%20separate%20worksheets.%20Having%20one%20query%20which%20takes%20data%20from%20SQL%2C%20based%20on%20it%20you%20may%20generate%20couple%20of%20more%20queries%20(or%20more%20taking%20into%20account%20other%20supporting%20queries)%2C%20each%20of%20them%20transforms%20data%20receiving%20by%20first%20query%20and%20lands%20result%20into%20the%20separate%20table.%20Something%20like%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Nima Mohandesan
Occasional Contributor

Hi,

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. 

 

3 Replies

@Nima Mohandesan 

Perhaps you may shift on Power Query to import data from SQL server and transform them into another two tables. When everything will be updated with each refresh.

@Sergei Baklan I could bring in the data via Power Query, but how does it help with those other two tables in Excel? They still need to be worksheets. Can you please elaborate? 

@Nima Mohandesan 

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies