getting data from other spreadsheets

[Roger]

I'd like to be able to get data from a number of spreadsheets that are all
copies of a master but have different data in the same cell locations. ie
Cell a1 in all spreadsheets have different data, altho the spreadsheets are
all formatted and laid out the same.

I'd like to be able to just put the filename in a column, and use vlookup to
access various areas of each spreadsheet using the filename. Thus if the
filename is changed or another added, its a simple matter of copying the row
above down to the next row, which would then pick up the new filename and
add the data from that file.

I've tried the ='C:\Data\[test1.xls]Sheet1'!$A1

but I can't seem to reference the [test1.xls] filename properly in the
master spreadsheet.

Any help would be appreciated.

Roger

 

[papou]

Hi Roger
Assuming file name starting in A1:
Formula in your master spreadsheet:
=INDIRECT("'C:\DATA\[" & A1 & "]Sheet1'!$A1")

HTH
Cordially
Pascal

 

[Roger]

Pascal hi
Thanks a million for that.
I was working down the path to use the INDIRECT function, but totally lost
with the formatting required within the square brackets.
Works a treat and many thanks again.
Regards
Roger

Hi Roger
Assuming file name starting in A1:
Formula in your master spreadsheet:
=INDIRECT("'C:\DATA\[" & A1 & "]Sheet1'!$A1")

HTH
Cordially
Pascal

I'd like to be able to get data from a number of spreadsheets that are all
copies of a master but have different data in the same cell locations. ie
Cell a1 in all spreadsheets have different data, altho the spreadsheets are
all formatted and laid out the same.

I'd like to be able to just put the filename in a column, and use

vlookup

to
access various areas of each spreadsheet using the filename. Thus if the
filename is changed or another added, its a simple matter of copying the row
above down to the next row, which would then pick up the new filename and
add the data from that file.

I've tried the ='C:\Data\[test1.xls]Sheet1'!$A1

but I can't seem to reference the [test1.xls] filename properly in the
master spreadsheet.

Any help would be appreciated.

Roger

 

[papou]

Hi Roger
You're welcome, glad it helped.

Cordially
Pascal

Pascal hi
Thanks a million for that.
I was working down the path to use the INDIRECT function, but totally lost
with the formatting required within the square brackets.
Works a treat and many thanks again.
Regards
Roger

Hi Roger
Assuming file name starting in A1:
Formula in your master spreadsheet:
=INDIRECT("'C:\DATA\[" & A1 & "]Sheet1'!$A1")

HTH
Cordially
Pascal

I'd like to be able to get data from a number of spreadsheets that are all
copies of a master but have different data in the same cell locations. ie
Cell a1 in all spreadsheets have different data, altho the

spreadsheets
are

all formatted and laid out the same.

I'd like to be able to just put the filename in a column, and use

vlookup

to
access various areas of each spreadsheet using the filename. Thus if the
filename is changed or another added, its a simple matter of copying

the
row

above down to the next row, which would then pick up the new filename and
add the data from that file.

I've tried the ='C:\Data\[test1.xls]Sheet1'!$A1

but I can't seem to reference the [test1.xls] filename properly in the
master spreadsheet.

Any help would be appreciated.

Roger

 

[Dave Peterson]

If those other files are closed, you may want to look at this from Harlan Grove:


http://www.google.com/[email protected]

I'd like to be able to get data from a number of spreadsheets that are all
copies of a master but have different data in the same cell locations. ie
Cell a1 in all spreadsheets have different data, altho the spreadsheets are
all formatted and laid out the same.

I'd like to be able to just put the filename in a column, and use vlookup to
access various areas of each spreadsheet using the filename. Thus if the
filename is changed or another added, its a simple matter of copying the row
above down to the next row, which would then pick up the new filename and
add the data from that file.

I've tried the ='C:\Data\[test1.xls]Sheet1'!$A1

but I can't seem to reference the [test1.xls] filename properly in the
master spreadsheet.

Any help would be appreciated.

Roger