I have the following formula in a worksheet (MAIN):
V:\office files\Registration Forms\[cap1.xls]Sheet1'!$B$22

I want the next row, same cell (column) in MAIN to have the following
formula:
V:\office files\Registration Forms\[cap2.xls]Sheet1'!$B$22

and then on down the column increasing exponentially (cap3, cap4, cap5
etc)....where cap1 and cap2 etc are other worksheets associated with
the main worksheet.

Is there a way to do this automatically (generate the formula
automatically), rather than having to do it manually?

Any help is very much appreciated.