Retrieve Data Based on Data Validation
Retrieve Data Based on Data Validation
I am in the process of creating a master inventory list for various classes and a quarterly reporting page. I have a total of 5 sheets: Master, Quarter 1, Quarter 2, Quarter 3, and Quarter 4, each containing information for four different classes.
Essentially, on each Quarter’s reporting page, I want to select the appropriate class (I’ve already implemented data validation for this) and have the data for that class pulled from the Master List. For example, if I choose “Pre K & Kindergarten,” a specific set of data should populate. If I then choose “1st and 2nd Grade,” the corresponding data should change accordingly.
I have attached a sample file. The first sheet is my master list, and the second is the reporting page. The highlighted section is where the data validation is applied, and the red section is where I’d like the corresponding data to appear.
Master Sheet: [Insert link or description]
Retrieve Data Based on Data Validation
Yr1- Q1 Sheet: [Insert link or description]
Retrieve Data Based on Data Validation
To accomplish this, we utilize the “INDEX” and “MATCH” functions. The Index function helps retrieve the desired value, while the Match function is used to locate the appropriate dimensions (row and column).
Retrieve Data Based on Data Validation
Please follow the steps below:
Enter the following 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 into the range E14:E24.
Now, when you change the class, the numbers will automatically update accordingly.
