I need a formula that can pull sets of data from multiple sheets.

[maribel12321]

I have 5 tabs (Kinder STAR, 1st grade, 2nd grade, 3rd Grade, 4th grade, 5th grade). Within each tab are student names and test scores for different reading assessments. I need to be able to pull the data and create a graph with select students. What I need is a formula that will pull the all the testing scores based on the student name from any of the tabs. I have a formular for the 5th grade tab here.

=index('5th Grade'!AP:AP,Match(1,($A$1='5th Grade'!D😀)*(C4='5th Grade'!C:C),0))

So how can I get it to work to pull data from all the tabs and not just the 5th grade one?

 

[maribel12321]

=index('5th Grade'!B:B,Match(1,($A$1='5th Grade'!D: D)*(C4='5th Grade'!C:C),0))

 

[Ralston18]

Workbook being named something like "Reading Assessments" with worksheets = Kinder STAR, 1st grade, 2nd grade, 3rd Grade, 4th grade, and 5th grade. Correct?

Does Find work?

https://www.computerhope.com/issues/ch000463.htm

Select Workbook and search for (FIND) any given student name. Start with a 5th grader, if applicable, to determine if the student's earlier grade test scores are found.

= = = =

The following link may prove helpful:

https://www.extendoffice.com/documents/excel/5017-excel-collect-data-from-multiple-sheets.html

There are a number of ways to make the process work - details and circumstances likely will make it all a bit cumbersome.

Try to see what works via the above links using a copy of your Workbook.

Just finding retrieving the data is a start.

Getting the data into the required viewable format is the next step.

Question: have you considered a database? Overall a database may be much more fitting to data entry, manipulation, and reporting. Plus very likely that your existing data can be imported into the database or you could link database and spreadsheet.

Just some starter thoughts. There may be other ideas and suggestions.

 

[ex_bubblehead]

As indicated above, you're using the wrong tool for the wrong job, much like trying to use a hammer to dig a hole in the ground. You should be using a database to store your data and you can then use the database functions of the spreadsheet to display the data any way you want. Yes, you can do it the way you are attempting but it's not going to be easy and will be quite error prone.

 

[USAFRet]

Absolutely correct as the above.
This is primarily a database function, not a spreadsheet function.

Each student has a record. Name, grade, test scores.
Numbers are then easily extracable by grade and/or which specific test.


I see this thing ALL the time at work.
A spreadsheet looks easy. All the data in straight columns, etc.
And then the usability quickly outruns the function of a spreadsheet.