i have 50 over worksheets and they are all the same format.

now , i want to consolidate all the datas( from the 50 sheets) into one
worksheet.

now, i am using =sheet1!A1+sheet2!A1......sheet50!A1 to calculate the
output

is there a better way of doing this? something like
=sum(sheet1!:sheet50!)_all

the_cells_in_A1 without having to point to the exact cell of each
worksheet?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=479020

Hi CJ,

You are nearly there.

Dave Peterson nicely explained the technique in a recent post:

'=====================
I like this technique...

Create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

=sum(start:end!B37)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheet so that people don't use it for real data
<<========================


---
Regards,
Norman



"cjjoo" wrote in
message ...

i have 50 over worksheets and they are all the same format.

now , i want to consolidate all the datas( from the 50 sheets) into one
worksheet.

now, i am using =sheet1!A1+sheet2!A1......sheet50!A1 to calculate the
output

is there a better way of doing this? something like
=sum(sheet1!:sheet50!)_all

the_cells_in_A1 without having to point to the exact cell of each
worksheet?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=479020

cjjoo Wrote:
i have 50 over worksheets and they are all the same format.

now , i want to consolidate all the datas( from the 50 sheets) into one
worksheet.

now, i am using =sheet1!A1+sheet2!A1......sheet50!A1 to calculate the
output

is there a better way of doing this? something like
=sum(sheet1!:sheet50!)_all

the_cells_in_A1 without having to point to the exact cell of each
worksheet?

Hi again cjjoo

You are nearly there, this is what you need

=SUM(Sheet1:Sheet3!A1) change A1 to suit your requirements

you can then copy this formula for other cells


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=479020