Trouble executing a formula

I am trying to do a lookup of sorts and I need to do this for every day of the year and for several people in the department. Rather than creating a different formula for each date, I've decided to spend time trying to come up with a formula that will do this for me. It might have taken less time to write 365 formulas though...

The constants are the location of the outside worksheets I will pulling the information from, and the location of the specific cell in each worksheet. What changes is the specific file location for each person, their initials and of course the date. The file name looks something like this:

C:\Reports\Bob\010108 Daily BB.xls

What I have been trying to do was to create a formula that will put in the name and initials of each person and if possible the date as well. Because of the way the date messes up my formula I changed my date cells to be text with the date listed as 010108... it's not pretty but it will do.

If I type in the following formula I am able to get the correct return of information:

='C:\Reports\Bob\[010108 Daily BB.Xls]Daily Sheet'!$B$3

Now I could not figure out how to paste this in and make the date go up by one... so I could recreate it 365 times and change the dates then do a "Find/Replace" for each person's name and initials... but come on this is Excel there has got to be a way right?

So yes, this has been my headache for the last couple of days. I tried to build a formula to return the above string that will reference the name, initials and date. I am able to pull the name and initials from elsewhere on the sheet and this is the formula that I used to build the string:

="'C:\Reports\" & $E$59 & "\[" & D2 & " Daily " & $E$61 &".xls]Daily Sheet'!$B$3"

This formula works beautifully and returns the string exactly as I first wrote it... but it doesn't actually execute the lookup. I should get a return in the cell of a number value from the B3 cell of the other worksheet.

I have tried to do a formula that will reference the results of the string, but it only displays the same string.

So my question is, how can I make the string that is returned actually execute as a formula too?

Any help or ideas is greatly appreciated.

I had not previously tried that. Thank you for giving me a new direction to investigate. My first attempt to use INDIRECT did not work. It returned with #REF!... The help files gave a possible reasons why INDIRECT would return this so I have tried to correct both those problems but no luck so far.

I'm going to keep testing though.