Simple link to external xls file

[jogeor]

a1: 210.xls
a2: 211.xls
a3: 250.xls
....
an: 335.xls

In my master xls file, I want to link a cell from each of the above files listed in column A.

If I type (say to cell B1) =[210.xls]sheet1!$B$6 I can get the value of cell "B6" of "sheet1" of file "210.xls"

Now my question:
Is there a way to use values from column A inside the brackets?
I tried ="["&A1&"]... but it doesn't work.

Also, I do not want to use INDIRECT(), INDIRECT.EXT, or Harlan Grove’s PULL() function.

Can you help?

 

[jasonb75]

Now my question:
Is there a way to use values from column A inside the brackets?
I tried ="["&A1&"]... but it doesn't work.

Not without using one of the functions that you don't want to use, or creating a new one the same as those.

 

[Ruddles]

I do not want to use INDIRECT()...Can you help?

Why not?

 

[jogeor]

jasonb75,
Thanks

Ruddles,
I finally used INDIRECT()...

The problem is, I must have more than 100 files open, otherwise INDIRECT() gives an error message.

I also tried PULL() but it is very-very slow...

I have 20+ master files each of which has 800 cells to pull from each one of the 100 files.

I finally got my job done, but it is slow and sometimes crashes EXCEL.

More than that... it is not elegant!

Still looking.

 

[jasonb75]

How about writing the direct version using vba?

[b1].formula ="=[" & [A1] & "]sheet1!$B$6"

 

[jogeor]

I can't figure it out!
But wouldn't it be similar to Pull() or INDIRECT.TXT? Sort of?

 

[jasonb75]

No, it bypasses those methods and writes the formula to the cell as if you had written it the normal way, when I said VBA, I meant as in macro, not custom function.

The formula you would see would be =[210.xls]sheet1!$B$6

 

[jasonb75]

A quick example for you to try in a dummy / test workbook.

Enter workbook names in A1:A6, then run the code

Sub not_so_indirect()
Dim mCell As Range, xlC As Variant
With Application
    xlC = .Calculation
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
For Each mCell In Range("B1:B6")
    mCell.FormulaR1C1 = "=[" & mCell.Offset(, -1) & ".xls]sheet1!R6C2"
Next
    .ScreenUpdating = True
    .Calculation = xlC
End With
End Sub

 

[jogeor]

Works perfectly.

Thanks!