how to copy a cell formula contain sheet name which have to add up automatically

I'm currently using microsoft excel 2003.
How do I make a formula that will keep added up after I copy the cell to another cell below.

For Example :
I type ='sheet1'!A1 after copy to the cell below it will become :
='sheet1'!A1
='sheet2'!A1
='sheet3'!A1
and keep adding up the number when I copy it

until now when I copy, it only add up the A1 to A2, A3, A4 . . .

the 'Sheet1' is the name of sheet contain information I need to use which located at A1.
just like I type 100 in A1 of sheet1. type 200 in A1 of sheet2. type 300 in A1 of sheet3.
Then in another sheet (I name the sheet "Total"),
in the sheet of Total, I want to show All the value of A1 in all sheet (sheet1, sheet2 and sheet3), which I want them sorted vertical,
so it will shown
100 (this is automatically by typing formula ='sheet1'!A1)
200 (this is automatically by typing formula ='sheet2'!A1)
300 (this is automatically by typing formula ='sheet3'!A1)

but it's really tired, if I always have to type manually the ='sheet4'!A1, then after that ='sheet5'!A1, then keep going until sheet100. I have around 100 sheet that need to shown the value of A1

Then would solve my problem if there are anyway to just copy down the formula, and the sheet number would change automatically when I copy it so no need to type manually.

I know this is quite confuse. I hope everyone can understand.

Your help is appreciated
Thanks in advance

try:

Please Login or Register  to view this content.

In which case adapt the ROW reference as necessary

=INDIRECT("'"&2010+(ROWS(A$1:A1)-1)&"'!A1")
copied down

DELETED, should open a new thread instead of reply

You need to simply adjust the increment from 2010 such that it goes in increments of 3 rather than 1

=INDIRECT("'"&2010+3*(ROWS(A$1:A1)-1)&"'!A1")
copied down

kingirfan,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.