Make an 'dynamic' copy (link) of a table to another sheet

[stanislasdenijs]

Hi all,

I have a simple yet complicated question.

I made a very big table/database on the first tab of an excel sheet. This database is full of formulas etc. and not prety, nor handy to use.

I want to create a second tab in the same file where i can see all the data from the previous tab, but it should also change with the table (the size more importantly) of the table in the first tab.

a simple 'equals to' doesn't work, as data changes constantly in the first tab, the table gets bigger every day.

the table in the second tab should also have some filters, to sort the data retrieved from the first tab.

I tried fiddling around with pivot tables, but the problem is that you can not simply show data, but always the sum of data etc. It also can't show simple text.

It would be great if you guys found a solution to this problem! I hope everything is clear from my explanation.

 

[GlennUK]

Why don't you have your second tab in a separate workbook, and populate by using MS Query to get the data from the database?

 

[SamTYler]

Copy the table, select top left corner of area you want "Dynamic table", then Paste Special; click Paste Link button on Paste control

Warning! Do NOT try to paste an entire sheets worth of links at once!

 

[stanislasdenijs]

Why don't you have your second tab in a separate workbook, and populate by using MS Query to get the data from the database?

Thank you a MILLION times! works like a charm, perfect!

 

[GlennUK]

Glad that you liked that idea!!!!

 

[Quattromeister]

Copy the table, select top left corner of area you want "Dynamic table", then Paste Special; click Paste Link button on Paste control

Warning! Do NOT try to paste an entire sheets worth of links at once!

This is really helpful! First post here.

Follow on Question... On my first sheet I have data filled in from another application. The data is dynamic as it is coming from a CRM and is the Product Description, Price, Qty, etc. and can only be populated on the first sheet. Since there is a lot of data coming of other sources that also is placed on the first sheet I would like the secondary sheet to be the printable quote and therefore would like to copy the dynamic area which has the Products to the secondary sheet. I hope this makes sense, I really do not want to go the macro route as some organizations do not allow them. Thanks in advance.

 

[Gabrielle_erre]

Any idea about how to paste with link only the used rows or first 1,000 rows with copy-paste?