Retrieving Cell Data Using Data Validation. I’m in the process of establishing a comprehensive curriculum inventory for multiple classes across four quarters. This involves five sheets—Master, Quarter 1, Quarter 2, Quarter 3, and Quarter 4—each accommodating four classes.
The objective is to select a specific class on the reporting page of each quarter (utilizing existing data validation) and have the corresponding data from the Master List transfer over. For instance, picking Pre K & Kindergarten should populate a specific set of data, and choosing 1st and 2nd Grade should adjust the displayed figures accordingly.
I’ve shared a sample file: the first page contains the master list, and the second is the reporting page. The highlighted section showcases the data validation, while the red area indicates where I aim to display the relevant numbers.
Retrieving Cell Data Using Data Validation
On the Master Sheet:
Yr1- Q1 Sheet:
To accomplish this, I’m leveraging the “INDEX” and “MATCH” functions. The ‘INDEX’ function retrieves the value, while ‘MATCH’ helps locate the correct position within the dimensions (rows and columns).
Here’s how to execute it: Retrieving Cell Data Using Data Validation
- 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 across the range E14:E24.
These formulas will dynamically update the numbers as you change the selected class, ensuring the corresponding data is reflected automatically.
