Usinf cells for filename
 

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?

Usinf cells for filename
 

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



"Mike Punko" wrote in message
...
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?

Usinf cells for filename
 

Any ideal what woudl work with a closed workbook?

"Tom Ogilvy" wrote:

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



"Mike Punko" wrote in message
...
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?

Usinf cells for filename
 

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


"Mike Punko" wrote in message
...
Any ideal what woudl work with a closed workbook?

"Tom Ogilvy" wrote:

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



"Mike Punko" wrote in message
...
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?