Linking Sheets with Filenames in cells 2

[BIGALINWALES]

Hey,

I have a list of filenames and I need to retrieve the value of E1 from Sheet1 for each of them.

Where =[test.xls]Sheet1!$E$1 would normally work, I need something like =[#cellA1#]Sheet1!$E$1.

Does anybody have any idea's? I've tried everything that I can think of.

Thanks in advance

Alex

 

[GlennUK]

The methods for doing this usually involve having all the files open. That's true when using INDIRECT , which is the usual method for doing this kind of thing.

Here's an example:

=INDIRECT("'["&A1&"]Sheet1'!$E$1")

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[BIGALINWALES]

Thanks Glenn,

That does work but is there anyway to do it without having all the files open?
Theres about 290 in total!

Thanks for the help.

Alex

 

[GlennUK]

Do you need to have the links to the 290 files be dynamic?

Or do you want to create the links by formulae to save typing, and then convert the links to be permanent links? With permanent links the files don't have to be open to refresh the links.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[BIGALINWALES]

The filenames are created using a CONCATENATE() into column A. The filename is a combination of plain text and a reference number that is unique per row.
This reference number shouldn't change so as long as you can create a permanent link using a formula that should be fine.
Thanks again

Alex

 

[GlennUK]

Hi Alex,

firstly create the text of the permanent links, by using formulae like this:

=("='["&A1&"]Sheet1'!$E$1")

copied down as far as you need.

Then select the area with the formulae, do edit copy and then edit pastespecial values. The selection will now contain strings that look like link formulae.

With the supporting files open ....
With the area selected, do Edit/Replace and put an = in the from and the to boxes ( in other words the results will be no different to the original ), and click the Change All button. This visits every cell in the selection, and performs a change that causes Excel to treat the string as a cell entry, making the strings permanent external links.

The reason you have to have the files open at this point is that the strings don't have disk/folder location information, and so the files will be expected to be in the Excel workspace. Once the links have been created, the files can remain closed while updating links.

Let me know how you get on.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[BIGALINWALES]

Thanks Glenn

That works great.
The replacing the = is a very useful tip for future use. I've tried and failed on many occasions to try and get excel to do the same thing.

Thanks again

Alex