Referencing a Range Name from a different spreadsheet [duplicate]

Question

I would like to create a spreadsheet that contains name, phone number, address and email address of many individuals. I will define the data as a range named "Index".

Then I would like to create a multitude of spreadsheets that contains some of the names and pull the phone numbers, email address etc from the spreadsheet that contains the various data.

The reason for doing this is that I would have one place to store emails, phone numbers, names so I would not have to make changes to the multitude of files if the email address changes.

Answer 1

Taking index as a keyword rather than range name and guessing that you would like details one person at a time rather than the complete set each time I suggest:

With a sheet (I've named 37372) laid out like so as your reference sheet:-

then with this formula in another sheet in ColumnB and say Row3 and copied across to ColumnE:-

=vlookup($A3,'37372'!$A:$E,column(),0)  

should provide the relevant details for say Jane when 2 is entered in A3.

If the formula is not in ColumnB I suggest the same process but then change column() to 2, 3, 4 and 5 as the formula is in columns further to the right.

Answer 2

I suspect your requirement can be better fulfilled with Filter views, a feature of the new Google Spreadsheets:

When creating a filter view, click on a column header, and define the filter:

When done, click the close the filter editor, and your filter view will be available from Data → Tools → Filter views:

And, of course, the filter view updates automatically when the data in the spreadsheet is changed.