Variable file reference

M

Michael

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.
 
M

Michael

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"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top