I am trying to use a date field in a column header to point to a file name to
pull data from and cannot get the formula to execute. Example:

B2 =10/2/08 B3=10/3/08 B4=10/4/08 ... etc....

formula in B3 = "=CONCATENATE("='Report
",MONTH(GL$3)&"-"&DAY(GL$3)&"-"&YEAR(GL$3),".xls'!Data"

where the report is stored as "Report 10-2-08.xls" and referencing the named
field "Data".

If I hard code the line the formula works and the Data appears in B3 but
the above line only shows the text in the cell and not the data referenced.

What am I doing wrong?

Thanks in advance for your efforts.

change YEAR(GL$3) to right(year(gl$3),2)




On Oct 7, 1:41*am, Michael wrote:
I am trying to use a date field in a column header to point to a file name to
pull data from and cannot get the formula to execute. *Example:

B2 =10/2/08 *B3=10/3/08 B4=10/4/08 ... etc....

formula in B3 = "=CONCATENATE("='Report
",MONTH(GL$3)&"-"&DAY(GL$3)&"-"&YEAR(GL$3),".xls'!Data"

where the report is stored as "Report 10-2-08.xls" and referencing the named
field "Data".

If I hard code the line the formula works and the Data appears in B3 but
the above line only shows the text in the cell and not the data referenced.

What am I doing wrong?

Thanks in advance for your efforts.

Okay, that fixed the year reference but the cell only displays the file name
and doesn't pull the value from the file. How do I get the cell to
execute the command to pull the data from the reference file?

Cell contents with the fix:
formula in B3 = "=CONCATENATE("='Report
",MONTH(B$3)&"-"&DAY(B$3)&"-"&RIGHT(YEAR(B$3),2),".xls'!Data"



"muddan madhu" wrote:

change YEAR(GL$3) to right(year(gl$3),2)




On Oct 7, 1:41 am, Michael wrote:
I am trying to use a date field in a column header to point to a file name to
pull data from and cannot get the formula to execute. Example:

B2 =10/2/08 B3=10/3/08 B4=10/4/08 ... etc....

formula in B3 = "=CONCATENATE("='Report
",MONTH(B$3)&"-"&DAY(B$3)&"-"&YEAR(B$3),".xls'!Data"

where the report is stored as "Report 10-2-08.xls" and referencing the named
field "Data".

If I hard code the line the formula works and the Data appears in B3 but
the above line only shows the text in the cell and not the data referenced.

What am I doing wrong?

Thanks in advance for your efforts.