Sorting Students Names and Attributes

Hello Everyone,

I've run into a bit of problem when trying to reference cells in different sheets. So, what I have are 20 sheets with nineteen sheets corresponding to a student (with sheet's name as the student's [with spaces]). The sheets corresponding to the individual students have the names and attributes on the same row. On the twentieth sheet I'm trying to create a column of cells of all the student's names and corresponding attributes in the adjacent columns (i.e., grades), which is essentially looking at the class as a whole. Everytime I try referencing the cells and do a copy spread action, into a column of cells in the overview sheet, from the student sheets I get a #NAME? or an Err.509.

Can some one please help me figure this conundrum out? If you need more information, please let me know.

Thank you!


Example_Spread.xlsx

Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Have you considered swapping things around so that you have ALL data on 1 sheet, instead of 19 different sheets, and then running your summaries from that?

You could still have all data on 1 sheet, then set up a summary sheet to pull in the student info based on just entering their name or number. That would then be the sheet that gets viewed.

Upload a small dummy file and I will show you what I mean

In the attached, I have 2 different suggestions.

1. I added 2 new sheets - Summary and Data
Data is all students in 1 sheet
Summary pulls into for all students (these 2 sheets, based on your sample, are essentially the same thing)

2. I used a UDF (User Defined Function - a macro/VBA) to extract each sheet name into a list.
Then I used INDIRECT() based on that list, to pull from each sheet.

See which 1 works better for you

Happy to help, thanks for the feedback