Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

Excel Help - Data from another XLS file

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Excel Help - Data from another XLS file

Old 02-22-06, 05:14 PM
  #1  
mbs
DVD Talk Platinum Edition
Thread Starter
 
mbs's Avatar
 
Join Date: Feb 2005
Posts: 3,519
Likes: 0
Received 0 Likes on 0 Posts
Excel Help - Data from another XLS file

I'm trying to get data from another XLS file. The issue is that I want to specify the filename (of the file with the original data) using the contents of a cell.

What I mean is, for example, say A1 has the value of 5. I want to be able to get the data then, from a file named 5.XLS. Is this possible?

I tried doing every combination I could think of along the lines of:

='$A$1.xls'!$D4

without success. If I replace $A$1 with the value that is in A1 it works fine. Any idea how I can use a cell call to get the filename?

Thanks for any help!
Old 02-22-06, 08:30 PM
  #2  
Senior Member
 
Join Date: Apr 2004
Location: Plainfield, IL ok, it's really Joliet
Posts: 617
Likes: 0
Received 0 Likes on 0 Posts
I think you have to have the name of workbook and cell in the formula. In addition i believe both sheets have to be open simultaneously.
Old 02-22-06, 10:38 PM
  #3  
Senior Member
 
Join Date: Mar 2003
Posts: 789
Likes: 0
Received 0 Likes on 0 Posts
Originally Posted by mbs
I'm trying to get data from another XLS file. The issue is that I want to specify the filename (of the file with the original data) using the contents of a cell.

What I mean is, for example, say A1 has the value of 5. I want to be able to get the data then, from a file named 5.XLS. Is this possible?

I tried doing every combination I could think of along the lines of:

='$A$1.xls'!$D4

without success. If I replace $A$1 with the value that is in A1 it works fine. Any idea how I can use a cell call to get the filename?

Thanks for any help!
I believe what you want to do is possible with the INDIRECT function. Just look it up in the helpfile of excel, it gives some good examples.
Old 02-24-06, 12:59 PM
  #4  
Member
 
Join Date: Aug 2000
Posts: 227
Likes: 0
Received 0 Likes on 0 Posts
Try this:

=INDIRECT("["&TEXT(A1,0)&".xls]Sheet1!B1")


where:
A1 is the cell in the current spreadsheet containing the name(number) of the file you want to get the data from.

B1 is the cell containing the value you want in the file you want to get the data from.

Hope that works!
Old 02-24-06, 01:43 PM
  #5  
mbs
DVD Talk Platinum Edition
Thread Starter
 
mbs's Avatar
 
Join Date: Feb 2005
Posts: 3,519
Likes: 0
Received 0 Likes on 0 Posts
Originally Posted by buffotoad
Try this:

=INDIRECT("["&TEXT(A1,0)&".xls]Sheet1!B1")


where:
A1 is the cell in the current spreadsheet containing the name(number) of the file you want to get the data from.

B1 is the cell containing the value you want in the file you want to get the data from.

Hope that works!
Thanks for the help! Your tip is definately on the right track (although only gives me #REF! at the moment). I think the trick does lie in the INDIRECT function. With this lead, hopefully I can figure it out after lunch.

Thanks again for the leads!
Old 02-24-06, 05:23 PM
  #6  
DVD Talk Special Edition
 
Join Date: Apr 2002
Location: Colorado Springs, CO
Posts: 1,086
Likes: 0
Received 0 Likes on 0 Posts
Maybe try this?:

=[yourfilename.xls]Sheet1!$A$1

Oops. After re-reading your original post I don't think this is what you want.

After Googling I found this:

The Cell function returns information about the formatting, location, or contents of the upper-left cell in a reference.

To get the sheet name:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

To get the workbook name:
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,(FIND("]",CELL("filename"))+1)-FIND("[",CELL("filename"))-2)

To get the path address & workbook name:
=CELL("filename")

To get the path address:
=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)



Hope it helps, but I have a feeling that it's still not what you're looking for.

Last edited by karnblack; 02-24-06 at 05:30 PM.

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.