MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to make file reference selectable


Posted by Dwight W. Reed, Jr. on March 12, 2001 5:10 PM

My employees fill out a summary excel worksheet of the days productivity numbers and save them under the file name Tally03-01-01.xls. I would like to write a formula where I can substitute the date in the below formula and have the Summary data change in my combined worksheet for a month. So in cell A1 I may have 03-02-01 and the formula below will change to reflect a different date and open a different file.

Any help would be appreciated.

='Tally03-01-01.xls'!T_L


Posted by Dave Hawley on March 13, 2001 12:45 AM


Hi Dwight

Try this

="'Tally" & A1 & ".xls'!T_L"

OzGrid Business Applications

Posted by Dwight W. Reed, Jr. on March 13, 2001 6:23 AM

That allowed the cell to change with what I put in A1 but it is treating that cell as text and does not show the value of T_L in the cell. Any ideas on that. I was thinking I am going to have to use a macro to do this but I want to see if I am forgetting something.

Posted by Dave Hawley on March 13, 2001 6:29 PM


Dwight
>but it is treating that cell as text
It is text!

>does not show the value of T_L
Is T_L sheet name or named cell ?


Dave

OzGrid Business Applications

Posted by Dwight W. Reed, Jr. on March 15, 2001 2:44 PM

It is a named cell in another worksheet.