collect data from multiple sheets into single sheet

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
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.
 

Forum statistics

Threads
1,141,222
Messages
5,705,109
Members
421,378
Latest member
CarlosDuran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top