File with a lot of links

[khaledocom]

Hi all,

In the attached Excel file (Please open):

The red colored part is changeable in formula part. Instead of changing the link (file name) manually every line, Is there a formula, code or macro can do this for me. Especially there are 3224 rows of formula for same Please help.
Pls see attached file.

[Simon Lloyd]

How will you know which number to use for which line?, there's no pattern to the numbering, when would you want to trigger the change?, do you have all these nubers stored somewhere?

[mdmackillop]

Check out the Indirect function.
Something like
=INDIRECT("'C:\Qtel\04895087\2010-April\QtelRecovery\Files\["& RIGHT(A6,22)&"]QtelRecovery" &"'!$C$7")

[khaledocom]

Thanks for your help,

I tried the above formula but it didn't work.

[Kenneth Hobs]

While you can set the full path, it matters little. Indirect() expects the file to be open or you get #ref. There is a way to use VBA to get data from a closed file.

String to Build where the fullpathname is in A6:
'C:\Bill\04895087\2010-April\BillRecovery\Files\[Mob-04-2010-100642.xls]Recovery'!$C$7
Formula:

=INDIRECT("'" & LEFT(A6,47) & "[" & RIGHT(A6, 22) & "]Recovery'!$C$7")

Or:

=INDIRECT("'" & "[" & RIGHT(A6, 22) & "]Recovery'!$C$7")

For Indirect() syntax see:
http://www.contextures.com/xlFunctions05.html

[khaledocom]

Thanks a lot Kenneth, but still not working showing "#Ref!".

Again I'm very thankful for your help.

[Kenneth Hobs]

If you built the string properly AND the file is open, AND the sheetname exists, then you won't get that error.

Type the path in manually if need be to check.

[mdmackillop]

Try the formula without the Indirect. You should see the desired address. Check it and correct for any errors
="'" & LEFT(A6,47) & "[" & RIGHT(A6, 22) & "]Recovery'!$C$7"

[khaledocom]

Although the file was opened but both formulas didn't work.

Thankful for your help.

[Kenneth Hobs]

Since we can't look over your shoulder, you will have to troubleshoot the problem yourself. mdmackillop showed you a trick to view your string for the Indirect() function. The link that I gave showed the syntax.

In my A6:
C:\Bill\04895087\2010-April\BillRecovery\Files\Mob-04-2010-100642.xls
I saved a file with name t:\test\Mob-04-2010-100642.xls and opened it. I did not have a sheet named Recovery so I used Sheet1. The path is not the same but Excel only allows one file with the same name to be open at once.

To check my formula: ="'" & "[" & RIGHT(A6, 22) & "]Sheet1'!$C$7"
Which resolves to: '[Mob-04-2010-100642.xls]Sheet1'!$C$7

The Indirect formula is then: =Indirect("'" & "[" & RIGHT(A6, 22) & "]Sheet1'!$C$7")

If I close Mob-04-2010-100642.xls, nothing is changed with the Indirect() value until a recalculation happens which will then show #REF.