Use INDIRECT as part of a cell link formula
Posted by JAF on December 27, 2001 3:32 AM
I have a large number of departmental directories, each of which contains an identical file.
I currently have a number of links to these individual files to create a monthly summary.
Each link (in column C) is as follows:
='G:\DepartmentData\2002\XYZ\[SUMS2002.xls]JANUARY'!BQ$107 (where XYZ is the name of each department.
I have a list of all the departments in Column B and what I'd like to do for each formula in Column C is to replace the XYZ element of the formula to pick up the text value of the cell in Column B on the same row.
I've tried ='G:\DepartmentData\2002\&INDIRECT(B2)&\[SUMS2002.xls]JANUARY'!BQ$107 but it doesn't work.