I'm hoping someone can help me. I have a worksheet that I want to pull
information into from 50 different worksheets. Basically, I'm pulling
fields from a registration worksheet that attendees are sending back.

Title First Name Last Name Badge Name Shirt Size


My formula looks like this: 'V:\office files\Travel Operations\2006
Programs\Capsugel Incentive\Registration Forms\[cap1.xls]Sheet1'!$B$21

where it is pulling from a saved registration (another xls file named
cap1) and pulling specific fields (b21, b22, etc), all across the row.

the next row, I want the same exact formula, but I want it to pull from
the next saved reg form (cap2), the next row to pull from cap2.xls, and
on down (50 total rows pulling from 50 different xls forms).

Is there a way that I can automatically increase from the cap1 to cap2
to cap3...or some other way to automatically change where the
individual row is pulling from without going in and changing each
cell's formula?

Any suggestions are much appreciated!