software

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.

TitleFirst NameLast Name Badge NameShirt SizeMy 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!These formulas worked for me - minor changes to fit your case
1) with number 1,2,3... in C1,C2,C3 .....
=INDIRECT(quot;'C:\Documents and Settings\Owner\My
Documents\[Cropquot;amp;C5amp;quot;.xls]Sheet1'!$D$1quot;)
OR
2) more general, unless someone adds row to top of worksheet
=INDIRECT(quot;'C:\Documents and Settings\Owner\My
Documents\[Cropquot;amp;ROW(A1)amp;quot;.xls]Sheet1'!$D$1quot;)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

gt; wrote in message oups.com...
gt; I'm hoping someone can help me. I have a worksheet that I want to pull
gt; information into from 50 different worksheets. Basically, I'm pulling
gt; fields from a registration worksheet that attendees are sending back.
gt;
gt; Title First Name Last Name Badge Name Shirt Size
gt;
gt;
gt; My formula looks like this: 'V:\office files\Travel Operations\2006
gt; Programs\Capsugel Incentive\Registration Forms\[cap1.xls]Sheet1'!$B$21
gt;
gt; where it is pulling from a saved registration (another xls file named
gt; cap1) and pulling specific fields (b21, b22, etc), all across the row.
gt;
gt; the next row, I want the same exact formula, but I want it to pull from
gt; the next saved reg form (cap2), the next row to pull from cap2.xls, and
gt; on down (50 total rows pulling from 50 different xls forms).
gt;
gt; Is there a way that I can automatically increase from the cap1 to cap2
gt; to cap3...or some other way to automatically change where the
gt; individual row is pulling from without going in and changing each
gt; cell's formula?
gt;
gt; Any suggestions are much appreciated!
gt;
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
gt;gt;gt;pulling from a different field in excel.That is exactly what my reply addressed
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

gt; wrote in message oups.com...
gt; This didn't seem to help. Maybe I didn't explain correctly?
gt;
gt; I am trying to create a worksheet that will draw the same data from 50
gt; separate saved worksheets (saved in a folder as cap1, cap2, cap3,
gt; etc.). Each row on the newly created worksheet should represent one of
gt; the 50 separate saved sheets. Rather than having to edit the formula in
gt; each cell to point to the next worksheet, I'm wondering if there is a
gt; way to automatically change which sheet the cell reference points to.
gt;
gt; So, the formula on one row reads:
gt; 'V:\office files\Travel Operations\2006 Programs\Capsugel
gt; Incentive\Registration Forms\[cap2.xls]Sheet1'!$B$21
gt;
gt; I'd like the formula in the same column, but on the next row to read:
gt; 'V:\office files\Travel Operations\2006 Programs\Capsugel
gt; Incentive\Registration Forms\[cap3.xls]Sheet1'!$B$21
gt;
gt; and then the next row to read:
gt; 'V:\office files\Travel Operations\2006 Programs\Capsugel
gt; Incentive\Registration Forms\[cap4.xls]Sheet1'!$B$21
gt;
gt; As you go across columns, the formula changes to:
gt; 'V:\office files\Travel Operations\2006 Programs\Capsugel
gt; Incentive\Registration Forms\[cap2.xls]Sheet1'!$B$22
gt;gt;gt;gt;pulling from a different field in excel.
gt;
well, perhaps I'm doing something wrong...it's not working for me.