Pull data from cells based on Data Validation

Original Question:-

How to pull data from cells by using the data validation?

I am working on creating a curriculum inventory master list for several classes as well as a reporting page for each Quarter. I have 5 sheets total- Master, Quarter 1, Quarter 2, Quarter 3, Quarter 4. And 4 classes for each quarter.

Basically what I want to do on each Quarter's reporting page is choose the correct class (have data validation already for this) and have the data for that class transfer from the Master List. So if I choose Pre K & Kindergarten, 1 set of data will be pulled and then if I choose 1st and 2nd Grade, the corresponding numbers will change.

I've attached a sample file. The first page is my master list. The second is the reporting page. The highlighted part is where the data validation is. The red is where I would like the corresponding numbers to go

Master Sheet:-


image 1


Yr1- Q1 Sheet:-


image 2


To meet this requirement we use the “INDEX” and “MATCH” function. Index function will help to lookup the value and Match function will use for those dimension (row and column).

Follow below given steps:-

  • Enter 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 range E14:E24.

image 3


When we will change the class numbers will get changed automatically.



Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.