auto Change link formula

[mikegust]

I would like to use the fill function to link many cells at once with different sheet names. If my formula looks like "='[Workbook_2014.xlsx]sheet1'!$A$1" how can I make it do something like "='[Workbook_2014.xlsx]sheet1+1'!$A$1" Then fill vertically to end up with "='[Workbook_2014.xlsx]sheet100'!$A$1". Thanks for any help.

[mrice]

Suggest looking at the INDIRECT function which will allow you build up the references as strings.

[sktneer]

May be this......

Please Login or Register  to view this content.

and then dopy down.

[mikegust]

Thanks for the quick reply. Didn't quite work for me. Tried =INDIRECT("[Workbook_2014.xlsx]Sheet"&ROWS($1:1)&"!A1") with the numbers I want in the rows ($1:1). Should the formula read whatever number is there and add it to the link?