(no subject)

dear lazyweb,

so i know how to make concatenated hyperlinks in excel, they look a lot like this:

=HYPERLINK(CONCATENATE("\\server\folder\folder\folder\",A2,".XLS"),A2)

where A2 contains the file name.

now, how do i make excel display a cell out of that workbook while using the concatenate function? Here's the written out formula without concatenation:

='\\server\folder\folder\folder\[filename.xls]Sheet1'!$F$5


this attempt just writes out the file path:
=CONCATENATE("'\\server\folder\folder\folder\[",a2,".xls]Sheet1'!$F$5")

this formula contains an error:
=CONCATENATE("'\\server\folder\folder\folder\[",a2,".xls]Sheet1'")!$F$5

and this formula returns a #ref, presumably because it can't find any file paths beginning with CONCATENATE:
='CONCATENATE("\\server\folder\folder\folder\[",a2,".xls]Sheet1")'!F5

can't figure out how i did it now, but i also came up with one that gave me the "update values" window that you get when you have a valid file path but invalid file name.

currently (non-concatenatedly) any time i need to extend the formula farther down the sheet, i have to take off the =', break it before .xls and paste the halves into two separate cells, extend the bit that ends in a number downward and copy the end bit downward to match, paste it into notepad and do a find and replace to put the =' back in, check to make sure the ' is still a ' and not ` or ´ or ‘ or ’, remove all the tabs, and paste back into excel.

there has to be an easier way.