I need a different formula that can be copied down

[ND Pard]

I have a worksheet with numbers 1 through 53 in cells A6 thru A58,
respectively.

In cell E6 is the following formula that works and updates without having to
open the reference file:

=INDEX('[01-1209.xls]SFN 119'!$A$13:$C$35,E$64,2)

Note: the first two numbers in the formula is the number in A6 written as 01
instead of 1.

I want to copy this formula down to row E58; the formual for
E7 should be:

=INDEX('[02-1209.xls]SFN 119'!$A$13:$C$35,E$64,2)

E8 should be:

=INDEX('[03-1209.xls]SFN 119'!$A$13:$C$35,E$64,2)

etc.

How can I rewrite the formula in E6 so it can be successfully copied down
without having to open all 53 files?
(I have gotten it to work using the Indirect function … but the Indirect
function worked ONLY if I have the referenced file open; and I don’t have
enough memory to open all 53 files at once.)

Your help is appreciated. Thanks.

 

[Luke M]

Unfortunately, you can't. INDIRECT is limited in that the workbook it
references must be open.

Here's a short macro that will create the formulas for you. Note that you
will still need to initially navigate to the workbook, as you are not
providing the full path name. You could get past that by writing the full
path name into the formula, i.e., into the macro (check what a linked formula
path looks like when the external workbook is closed)

'=======
Sub CreateLinks()
Dim r As Range
'Range of cells to place formulas in
Set r = Range("B6:B53")

For i = 1 To r.Count
FName = Format(i, "00")
For Each c In r
c.Formula = "=INDEX('[" & FName & "-1209.xls]SFN 119'!$A$13:$C$35,E$64,2)"
Next c
Next
End Sub

 

[ND Pard]

Thanks.

I guess I could have written the macro, and may use what you've given me.

Was hoping for a formula ...

Thanks again.

Unfortunately, you can't. INDIRECT is limited in that the workbook it
references must be open.

Here's a short macro that will create the formulas for you. Note that you
will still need to initially navigate to the workbook, as you are not
providing the full path name. You could get past that by writing the full
path name into the formula, i.e., into the macro (check what a linked formula
path looks like when the external workbook is closed)

'=======
Sub CreateLinks()
Dim r As Range
'Range of cells to place formulas in
Set r = Range("B6:B53")

For i = 1 To r.Count
FName = Format(i, "00")
For Each c In r
c.Formula = "=INDEX('[" & FName & "-1209.xls]SFN 119'!$A$13:$C$35,E$64,2)"
Next c
Next
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I have a worksheet with numbers 1 through 53 in cells A6 thru A58,
respectively.

In cell E6 is the following formula that works and updates without having to
open the reference file:

=INDEX('[01-1209.xls]SFN 119'!$A$13:$C$35,E$64,2)

Note: the first two numbers in the formula is the number in A6 written as 01
instead of 1.

I want to copy this formula down to row E58; the formual for
E7 should be:

=INDEX('[02-1209.xls]SFN 119'!$A$13:$C$35,E$64,2)

E8 should be:

=INDEX('[03-1209.xls]SFN 119'!$A$13:$C$35,E$64,2)

etc.

How can I rewrite the formula in E6 so it can be successfully copied down
without having to open all 53 files?
(I have gotten it to work using the Indirect function … but the Indirect
function worked ONLY if I have the referenced file open; and I don’t have
enough memory to open all 53 files at once.)

Your help is appreciated. Thanks.