Assume your source sheets are simply named as numbers: 1,2,3,... and you want
to retrieve the contents in cells B1, B2, B3, ... from each sheet into a
summary sheet

In your summary sheet,
List the source sheetnames in B1 across, eg: 1,2,3, ...
List the cell references in A2 down, eg: B1, B2, ...

Then place in B2:
=INDIRECT("'"&B$1&"'!"&$A2)
Copy B2 across/fill down to populate the contents from all the source sheets

If you need it with an error trap to return neat looking blanks ("") for any
source sheets not existing as yet, you could use instead in B2:
=IF(ISERROR(INDIRECT("'"&B$1&"'!"&$A2)),"",INDIREC T("'"&B$1&"'!"&$A2))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Narnimar" wrote:
I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
with similar format. The top row a1 to a10 has cells with heading . I add new
this similar sheets continuously with changing data in b1 to b10. Now can I
make sheet1 as index of those sheets and how to transfer pasting the contents
of b1 to b10 data of every sheet into the row cells of sheet1 automatically
as and when the sheets I add?