Automatically step a formula?

[sethridge]

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.

 

[Bernard Liengme]

Did you try
In A1:A10 the numbers 1,2,3,4,5...10
In your cells =INDIRECT("V:\office files\Registration
Forms\[cap"&A1&"1.xls]Sheet1'!$B$22")
and copy down the row
Any reference to a file requires the file to be open

By the way 1,2,3,4 is a linear increase, not exponential.

 

[sethridge]

You are right,sorry, linear.

I tried this...=INDIRECT("V:\office files\Registration
Forms\[cap"&A1&".xls]Sheet1'!$B$22") with the sequential numbers in the
A1-A10, etc.... it gives me a #ref. i've tried it with the other files
open/closed...i know i have done this before, not sure why i'm having
such a hard time with it now.

 

[Bernard Liengme]

REF indicates Excel is having problems locating the external file.
Open one of the files (CAP1); in the other file type = and then 'point' at
cell B22 in CAP1
This has to work!
Now modify the formula as I have suggested
best wishes

 

[sethridge]

This is the formula that ended up working:
=INDIRECT("[cap"&$A3&".xls]Sheet1!$B$22")

I think that you had an extra ' in there..you told me to put: In your
cells =INDIRECT("V:\office files\Registration
Forms\[cap"&A1&"1.xls]Sheet1'!$B$22")