I have one sheet with 100 rows of data.
I have a second sheet that will use the same data but with filters applied. In fact, I will have a dozen sheets, each with different filters.
My goal is to have references from the every sheet to the first so that prior to filtering, they all contain exactly the same data. This way I only have to modify one sheet and all sheets will reflect the changes.
The purpose is to create external links from word to excel to display specific rows, but there appears to be a limitation to linking where it displays absolutely everything that you see on the sheet itself (and each view must be different).
I can manually reference the first cell and then drag the black box to easily expand it to the required number of rows, but that would require me to go into each sheet and drag the black box again whenever I add new entries to the master copy.
Is there an easy way to do this?
Note that the issue is the same as https://superuser.com/questions/253263/easy-way-for-users-to-update-linked-documents-excel-2007, except this time I am using a different approach. Solutions to both would be welcome.
Best Answer
You can use 'Get External data'
Select 'From Other Sources' 'From Microsoft Query' then 'Excel data'
This allows you to create a query on another workbook, including all the power of sql.
Create as many queries as you need to the same source each with their own conditions