References Cells from Separate Files

[abusementpark]

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?

 

[Ohmly]

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

 

[abusementpark]

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?

 

[MortenA]

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

Best regards

Morten

 

[electricpete]

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.

 

[electricpete]

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.