getting data from other spreadsheets

  • Thread starter Thread starter Roger
  • Start date Start date
R

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
 
Hi Roger
Assuming file name starting in A1:
Formula in your master spreadsheet:
=INDIRECT("'C:\DATA\[" & A1 & "]Sheet1'!$A1")

HTH
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


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

HTH
Cordially
Pascal


Roger said:
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
Click to expand...
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
Click to expand...
Click to expand...
 
Hi Roger
You're welcome, glad it helped.

Cordially
Pascal

Roger said:
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


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

HTH
Cordially
Pascal


Roger said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
 
Back
Top