×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

References Cells from Separate Files

References Cells from Separate Files

References Cells from Separate Files

(OP)
Hey guys,

I am trying to figure how to reference cells into a spreadsheet from other files that are saved in the same folder.  Here is the basic problem:

We have this a spreadsheet (File 1) that calculates design wind pressures on buildings.  It always puts the values I need into certain cells.  I want the values in these cells to automatically be referenced into a another file (File 2) in the same folder.

My goal is to be able to type in the name of File 1 into a cell in file, so that File 2 knows where to get the numbers from.

Any tips?  Can this be done?

RE: References Cells from Separate Files

If the cell with the information is in A1 of Sheet 1 in a file called Book1.xls

The formula is.

=[Book1.xls]Sheet1!A1

RE: References Cells from Separate Files

(OP)
Ohmly,

Thanks for the response.  I understand what you are saying.  However, the problem I am having is that I want to type in the name of the file to be referenced into another cell.

Keeping with your example, let's say I type the file name, Book.xls, into cell B2 of the file.

So in the formula I would type

=[B2]Sheet1!A1

but it looks for a file named B2, not reference into that cell what is in cell B2, before looking, which is what I want it to do.  How do I get it to do what I want?  

RE: References Cells from Separate Files

i think you have to go via VB - here its not that difficult - both to get a list of files in a specific folder - and to get a filename for an open sheet.

Know i cheating a little - not writeing how but i have to go smile

Best regards

Morten

RE: References Cells from Separate Files

Into Cell B2 type: Book.xls

Now in another cell type:
=INDIRECT("["&$B$1&"]Sheet1!A1")

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: References Cells from Separate Files

Whoops, that doens't work unless the file is open. When the file is not open, the filename is supposed to be enclosed in single quotes.

When referencing directly, need to include filename in single quotes.  So indirect should look like this:
=INDIRECT("'C:\["& B1 & "]Sheet1'!A1")

But doens't seem to work.

I'm sure it can be done. But I gotta run.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources