Use INDIRECT as part of a cell link formula

Posted by JAF on December 27, 2001 3:32 AM

Hiya

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.

Any suggestions??

Posted by JAF on December 27, 2001 3:40 AM

Duh!! Just thought of an easy way!

Muppet brain here was trying to reference a cell directly in the formula (which may of course be possible)

The easier option is to create a macro to generate the formula as follows:

'highlight all required cells in Column C and run this macro to insert
'the formulas with reference to the correct departmental cost centre.
Sub InputFormulas()
For Each cel In Selection
cel.Formula = "='G:\DepartmentData\2002\" & cel.Offset(0, -1).Value & "\[SUMS2002.xls]JANUARY'!BQ$107"
Next cel
End Sub

Works a treat!!