Forum Discussion
HOW TO MAKE 3 TABLES RANGE DYNAMIC WITHOUT CONVERTING IT WITH TABLE
HI ALL,
AS I HAVE 3 TABLES IN FINAL DATA SHEET.
1ST IS DEAL SUMMARY TABLE WHICH IS EXIST IN DEAL SUMMRAY SHEET
2ND IS CONTACT LIST TABLE WHICH IS EXIST IN CONTACT LIST SHEET
3RD IS REFERENCE COMPANY TABLE WHICH IS EXIST IN REFERENCE COMPANY SHEET.
SO, I WANT THAT WHENEVER I ADD DATA IN ALL OF THE 3 SHEETS, I WANT THAT DATA SHOULD BE AUTOMATICALLY PULL IN THEIR APPROPRIATE TABLES IN FINAL DATA SHEET LIKE DEAL SUMMARY SHEET DATA SHOULD BE IN DEAL SUMMARY TABLE IN FINAL SHEET.CONTACT LIST SHEET DATA SHOULD BE IN CONTACT LIST TABLE AUTOMATICALLY AND SO ON.
AND ALSO, WHENEVER THE DATA PULL FROM THE SHEETS I WANT THAT TABLE AUTOMATICALLY INCREASE OR DECREASE THEIR RANGES ACOORDING TO DATA .ONE TABLE SHOULD NOT BE OVERLAP OTHER TABLE.
AND PLEASE NOTE ALL FUNCTIONS SHOULD BE UPTO EXCEL 2016. THATS MY REQUIREMENTS.
IF ANYONE CAN HELP ..PLS REPLY
9 Replies
If a given table is simply pulling from another sheet, you might try this:
'Feed' is where the data is coming from. In this sample I set it to 50 rows (Can be increased as needed). The formula determines how many blanks are in the range and drops them from the pull.
=LET(data,Feed!A1:K50,blanks,COUNTBLANK(TAKE(data,,1)),DROP(data,-blanks))- let function is from excel 365 i can't use it ... i can use only upto excel 2016
- IF those tables are 'Formatted as a Table' then the dynamic range of the table WILL automatically change but you will need to use proper table references: Table1[column 1]
I think they call it structured notation but once the data is formatted as a table (see home tab and click that button) then when you have a formula and you go to that table you can select the column and it will automatically insert that notation.
alternative 'old school' way:
=A$1:index(sheet1!A:A,counta(sheet1!A:A))- but my tables are one under the other
- Then all the more reason you should be formatting the tables as tables and using structured references.
