Usinf cells for filename

[Guest]

ok I have a cell with the function.

=CONCATENATE(TEXT(C3,"yyyymmmmdd"),".xls")

Which gies me something like this.

2005October14.xls

I want to use that for a cell referance file name.

='W:\Aurora Daily Production Report\2005\10_Oct\[2005October14.xls]Daily
Report'!$E5

How to I get ths to work?

 

[Tom Ogilvy]

Normally you would use the Indirect function, however it doesn't work with
closed workbooks If you open your workbook 2005October14.xls you can do:

=Indirect("'[" & CONCATENATE(TEXT(C3,"yyyymmmmdd"),".xls") & "]Daily
Report'!E5")

 

[Guest]

Any ideal what woudl work with a closed workbook?

Normally you would use the Indirect function, however it doesn't work with
closed workbooks If you open your workbook 2005October14.xls you can do:

=Indirect("'[" & CONCATENATE(TEXT(C3,"yyyymmmmdd"),".xls") & "]Daily
Report'!E5")

--
Regards,
Tom Ogilvy

ok I have a cell with the function.

=CONCATENATE(TEXT(C3,"yyyymmmmdd"),".xls")

Which gies me something like this.

2005October14.xls

I want to use that for a cell referance file name.

='W:\Aurora Daily Production Report\2005\10_Oct\[2005October14.xls]Daily
Report'!$E5

How to I get ths to work?

 

[Tom Ogilvy]

It isn't supported directly.

Possible solutions include writing a UDF in VBA. Harlan Grove has post such
a UDF. It opens a new instance of Excel, opens the subject file, retrieves
the data, closes the file, closes the new instance of excel. Probably not a
barn burner.

or you could write event code that takes the value in your cell and creates
a hard coded formula in the cell with the formula

--
Regards,
Tom Ogilvy

Any ideal what woudl work with a closed workbook?

Normally you would use the Indirect function, however it doesn't work with
closed workbooks If you open your workbook 2005October14.xls you can do:

=Indirect("'[" & CONCATENATE(TEXT(C3,"yyyymmmmdd"),".xls") & "]Daily
Report'!E5")

--
Regards,
Tom Ogilvy

ok I have a cell with the function.

=CONCATENATE(TEXT(C3,"yyyymmmmdd"),".xls")

Which gies me something like this.

2005October14.xls

I want to use that for a cell referance file name.

='W:\Aurora Daily Production Report\2005\10_Oct\[2005October14.xls]Daily
Report'!$E5

How to I get ths to work?