Excel 2007 : Updating Row in Reference Function

[roscokay]

I have the following formula:
='P:\HEALTH CARE\130163 KGH IHSC P3 RFP\Drawings\2 DD-CD\00-Revit\Links\01a BIMLink\[Room Data Arch Access.xlsx]Room Data Arch Access'!$C$2
In my excel worksheet there are more than 50 cells with this formula connecting to different cells in the referenced file. I have to create a sheet for each "Room", which connects to a row in the referenced file.

What I would like to do is set the formula to automatically update the row.

I'm hoping this makes sense... in simple terms I need the 2 to change to 3 in 50+ cells automatically.

[pmalen]

Use string concatenation and INDIRECT function to get what you need.

See the attached file and try it out.