Formula for cell reference in another file

[MPH]

I want to construct a formula to replicate the cell entry obtained when
pointing to a cell in another xls file.

I have 40 files with data on up to 3 pages in each file.

Each page with data has a unique name i.e. B123

Each page with data has an identical summary row, containing cells
containing values - relevant cells are D10 thru' X10.

All other value cells in these pages refer to inconsistent information,
hence, can't use consolidation.

I want a formula in another file that refers to ONE cell in another file.

All files will be in the same directory.

It is easy to refer to each cell in the new file by simply pointing at the
other cell, then to copy across. However, the file names are likely to
change frequently.

I have tried, & failed to create a formula in another file by writing the
file name e.g. file1.xls in B5 & the page name [e.g. B123] in C5 & the cell
[e.g. D10] in D5

Then construct a formula in E5, to pull through the value. Have used offset
and concatenate but so far have failed to replicate the formula. Have tried
with both files open.



Not sure whether the above is a good explanation; hope so.

I have struggles and failed with this and would very much appreciate help.

Thanks in anticipation.

 

[kletcho]

Try using the Indirect formula. For example this works:

=INDIRECT("'[Meeting Planner.xls]Sheet1'!$A$9")

You could provide the name of the workbook, sheet, and cell in cells
and then use something like this to dynamically refer to a workbook:

=INDIRECT("'["&C1& "]"&D1&"'!" &E1)

The trick is getting all the symbols in their too ( [ ] ! ' )

 

[mph999]

Thanks, your right, the symbols are very tricky. Copied + pasted you
reply to get it working.

The formula works, so long at the other file is open > #REF = whe
other file is closed.
Files are in same folder [network server currently].
Am I doing something wrong, or how, if at all, can I get it workin
without having "linked" files open?
Many thanks for eearlier response

 

[Pete_UK]

INDIRECT( ) will only work if the file is open.

Hope this helps.

Pete

 

[kletcho]

Let's step back a second. What is the purpose of having what seems to
be similar information in 40 different files? Do you have 40 different
people entering information? If so maybe you should look into shared
workbooks. Another great solution when you have multiple sources of
information is to use an Access database. Thoughts?

 

[Harlan Grove]

mph999 wrote...
....

The formula works, so long at the other file is open > #REF = when
other file is closed.
Files are in same folder [network server currently].
Am I doing something wrong, or how, if at all, can I get it working
without having "linked" files open?

....

To use INDIRECT, the other files must be open. There are alternatives
for working with closed files. See

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).

 

[Harlan Grove]

(e-mail address removed) wrote...

Let's step back a second. What is the purpose of having what seems to
be similar information in 40 different files? Do you have 40 different
people entering information? If so maybe you should look into shared
workbooks. . . .

Awkward if those 40 other people are in different locations connected
to different servers. And cycling through 40 different persons' entries
in the same cells and storing those entries in separate cells in some
other worksheet/workbook isn't a trivial matter.

. . . Another great solution when you have multiple sources of
information is to use an Access database. Thoughts?

Still a headache handling replication if the 40 users were in different
locations.

 

[MPH]

Thanks - very thought provoking.
Access - I don't have the skill level to use it effectively. I reckon I
would create as many problems [for me] as I would solve.
Your right, others are not all in same location.
Maybe I should have had shared files but they can be awkward.
Reckon I need to sleep on it. Perhaps Paste Special + link is the least
awkward option.
Nevertheless, I am impressed with the XL community, its willingness to
contribute and quality of responses.
I now have a way forward.