collect data from multiple sheets into single sheet

[Smithgall]

I have 50 sheets. each is the same "template" representing an employee performance. The sheet have teh same data that i wish to collect from each sheet and then make a table on sheet 1. Rather than hard code sheet1!A1 = JOHN!A1, and SHEET1!B1=JANE!A1 etc i would like to code sheet1!A1= "the first sheet in the group"A1, and then SHEET1!B1 = "the second sheet in the group"A1.

The reason for this is that right now (unless i can find a better way) i rank the emp by perfomance. So john is #1 and his sheet is labled based on rank and name so his sheet is 1-JOHN. my macros sorts the sheets by the tab name so the sheets are in order of 1-john, 2-jane etc. Once the sheets are sorted accordingly i have a chart that graphs their data and the person on the first sheet shoudl appear on the left side of the chart.

so since the sheets will change order occasionally i will need the sheet1 to read data from the first sheet to the last regardless of name. Does this make any sense? If so do you have a solution?

 

[Greg Truby]

Took me a minute to remember the right keyword to find it with Search...answered this a couple years back...

http://www.mrexcel.com/board2/viewtopic.php?t=118902

Disregard the bit about the SheetPos. The salient part is the WSNames named formula and then how to use that in INDEX() and put all of that inside an INDIRECT() to pull what you want.