Extracting Data Using Data Validation. I’m organizing a curriculum inventory across multiple classes and quarters, utilizing five sheets: Master, Quarter 1, Quarter 2, Quarter 3, and Quarter 4, each with four classes.
My goal is to select a class on the reporting pages for each quarter (already set up with data validation) and have the corresponding data from the Master List populate automatically. For instance, choosing Pre K & Kindergarten will pull specific data, while selecting 1st and 2nd Grade will display different information.
Extracting Data Using Data Validation
In the attached sample file, the Master List resides on the first page, and the second page illustrates the reporting section. The highlighted section shows where data validation is implemented, and the red section indicates where I want the relevant numbers to appear.
Using the “INDEX” and “MATCH” functions helps achieve this. “INDEX” retrieves the value, while “MATCH” determines the dimensions (row and column) for data extraction.
Steps:
- Input the formula in cell E13:
=INDEX(Master!$A$5:$M$9,MATCH('Yr1- Q1'!$E$5,Master!$A$5:$A$9,0),MATCH('Yr1- Q1'!$D13,Master!$A$5:$M$5,0))
- Copy the same formula in the range E14:E24.
This setup ensures that when the class selection changes, the corresponding data automatically updates accordingly.
