Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 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.

share|improve this question
    
One workaround I have is to drag that black box to cover an excessive amount of rows that is beyond reasonable limits of my data. I guess that would be enough for most purposes, but knowing how to link entire sheets would be useful. –  MxyL Mar 7 '11 at 16:34
    
If you are performing subselections of selections, and filtering upon filtering, you might consider using a database application rather than a spreadsheet to accomplish this. –  JeffG Mar 7 '11 at 17:28
    
I have considered that, but as most of the users here know very little about anything outside of word and excel and other common software I decided to stick with a spreadsheet so that it is very intuitive as they are the ones that will be managing this spreadsheet. –  MxyL Mar 10 '11 at 15:32
1  
Generally, I'd agree about databases, however if you have Microsoft Access, it requires a small amount of forethought to initially setup, but is as easy as Excel to work with on a day to day basis. It's a very approachable database. –  JeffG Mar 11 '11 at 0:21

1 Answer 1

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

share|improve this answer
    
Thanks, that works. However, it seems like it doesn't allow me to connect to files located on a sharepoint server. I mapped it to a local drive but it doesn't seem to be able to find it. –  MxyL Mar 10 '11 at 15:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.