How do I automatically increment worksheet number when pasting a .

I'm using Excel 2003. I have multiple similar worksheets in a workbook. I
want to summarize data from the similar worksheets to "master" worksheet also
in the same workbook. For example I want to extract the data from cell D2
from all the similar worksheets to a column on the master worksheet. This
formula (='Sheet1 (2)'!D2) will reference the data in Cell D2 from Sheet1
(2). If I copy and paste it in into multiple column cells Excel will
automatically increment the cell identifier D3, D4, D5 etc. Is it possible
to have Excel increment the Sheet identifier Sheet1 (3), Sheet1 (4), Sheet1
(5)..etc when doing a copy/paste. Or does anyone have a better idea to
automate this task?

Instead of having in say, B2: ='Sheet1 (2)'!D2

Try in B2: =INDIRECT("'Sheet1 ("&ROWS($A$1:A2)&")'!D2")
then copy B2 down

The above will return the desired incrementing
of the sheet # as you copy down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Mr Keldor" <Mr [email protected]> wrote in message
news:[email protected]...
> I'm using Excel 2003. I have multiple similar worksheets in a workbook. I
> want to summarize data from the similar worksheets to "master" worksheet
also
> in the same workbook. For example I want to extract the data from cell D2
> from all the similar worksheets to a column on the master worksheet. This
> formula (='Sheet1 (2)'!D2) will reference the data in Cell D2 from Sheet1
> (2). If I copy and paste it in into multiple column cells Excel will
> automatically increment the cell identifier D3, D4, D5 etc. Is it possible
> to have Excel increment the Sheet identifier Sheet1 (3), Sheet1 (4),
Sheet1
> (5)..etc when doing a copy/paste. Or does anyone have a better idea to
> automate this task?