Need help with automating the updating of a function in worksheet
 

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!

Need help with automating the updating of a function in worksheet
 

These formulas worked for me - minor changes to fit your case
1) with number 1,2,3... in C1,C2,C3 .....
=INDIRECT("'C:\Documents and Settings\Owner\My
Documents\[Crop"&C5&".xls]Sheet1'!$D$1")
OR
2) more general, unless someone adds row to top of worksheet
=INDIRECT("'C:\Documents and Settings\Owner\My
Documents\[Crop"&ROW(A1)&".xls]Sheet1'!$D$1")
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
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!

Need help with automating the updating of a function in worksheet
 

This didn't seem to help. Maybe I didn't explain correctly?

I am trying to create a worksheet that will draw the same data from 50
separate saved worksheets (saved in a folder as cap1, cap2, cap3,
etc.). Each row on the newly created worksheet should represent one of
the 50 separate saved sheets. Rather than having to edit the formula in
each cell to point to the next worksheet, I'm wondering if there is a
way to automatically change which sheet the cell reference points to.

So, the formula on one row reads:
'V:\office files\Travel Operations\2006 Programs\Capsugel
Incentive\Registration Forms\[cap2.xls]Sheet1'!$B$21

I'd like the formula in the same column, but on the next row to read:
'V:\office files\Travel Operations\2006 Programs\Capsugel
Incentive\Registration Forms\[cap3.xls]Sheet1'!$B$21

and then the next row to read:
'V:\office files\Travel Operations\2006 Programs\Capsugel
Incentive\Registration Forms\[cap4.xls]Sheet1'!$B$21

As you go across columns, the formula changes to:
'V:\office files\Travel Operations\2006 Programs\Capsugel
Incentive\Registration Forms\[cap2.xls]Sheet1'!$B$22
pulling from a different field in excel.

Need help with automating the updating of a function in worksheet
 

That is exactly what my reply addressed
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
This didn't seem to help. Maybe I didn't explain correctly?

I am trying to create a worksheet that will draw the same data from 50
separate saved worksheets (saved in a folder as cap1, cap2, cap3,
etc.). Each row on the newly created worksheet should represent one of
the 50 separate saved sheets. Rather than having to edit the formula in
each cell to point to the next worksheet, I'm wondering if there is a
way to automatically change which sheet the cell reference points to.

So, the formula on one row reads:
'V:\office files\Travel Operations\2006 Programs\Capsugel
Incentive\Registration Forms\[cap2.xls]Sheet1'!$B$21

I'd like the formula in the same column, but on the next row to read:
'V:\office files\Travel Operations\2006 Programs\Capsugel
Incentive\Registration Forms\[cap3.xls]Sheet1'!$B$21

and then the next row to read:
'V:\office files\Travel Operations\2006 Programs\Capsugel
Incentive\Registration Forms\[cap4.xls]Sheet1'!$B$21

As you go across columns, the formula changes to:
'V:\office files\Travel Operations\2006 Programs\Capsugel
Incentive\Registration Forms\[cap2.xls]Sheet1'!$B$22
pulling from a different field in excel.

Need help with automating the updating of a function in worksheet
 

well, perhaps I'm doing something wrong...it's not working for me.