HOW TO CREATE A TABLE THAT AUTOMATICALLY POPULATES DATA

June 17, 2020

I have received a lot of positive feedback on my “Automated Reports” post.

THANK YOU!

I also received many requests on how I got the data to auto-populate for me. So here you go!

Notice how I have four tabs on the bottom. The ones you will need are the WISC-IV (or name of any test you use), Standard Scores, and Scaled Scores.

For Standard Scores, I have the Standard Score in column A, the Percentile in column B, and the Descriptors in column C.

For Scaled Scores, I have the Scaled Score in column A, the Percentile in column B, and the Descriptors in column C.

SO NOW WE ENTER THE FORMULAS

STANDARD SCORE PERCENTILE EXAMPLE

In this example, I want D4 to automatically fill the percentile that matches the Standard Score in C4. So I use this formula:

=IF(ISNUMBER(MATCH(C4,‘STANDARD SCORES‘!A:A,0)),VLOOKUP(C4,‘STANDARD SCORES‘!A:B,2,0),“”)

C4 = The cell I want to match
Standard Scores= The tab I want to pull the data from
A:A,0 = I want it to look at the entire column A (to match), and 0 indicates I want that exact value
Now the VLOOKUP
A:B,2,0 = I want to look at the first two columns A (Standard Score) and B (Matching Percentile).
- I want it the second value and O = False (aka if you accidentally sort the column, it will still pull that number)

STANDARD SCORE DESCRIPTOR EXAMPLE

In this example, I want F4 to automatically fill the descriptor that matches the Standard Score in C4. So I use this formula:

=IF(ISNUMBER(MATCH(C4,‘STANDARD SCORES‘!A:A,0)),VLOOKUP(C4,‘STANDARD SCORES‘!A:C,3,0),“”)

C4 = The cell I want to match
Standard Scores= The tab I want to pull the data from
A:A,0 = I want it to look at the entire column A (to match), and 0 indicates I want that exact value
Now the VLOOKUP
A:C,3,0 = I want to look at the first three columns A (Standard Score), B (Matching Percentile), and C (Descriptor). I want the third value and 0 = False.

SCALED SCORE PERCENTILE EXAMPLE

In this example, I want D6 to automatically fill the percentile that matches the Scaled Score in C6. So I use this formula:

=IF(ISNUMBER(MATCH(C6,‘SCALED SCORES‘!A:A,0)),VLOOKUP(C6,‘SCALED SCORES‘!A:B,2,0),“”)

C6 = The cell I want to match
Scaled Scores= The tab I want to pull the data from
A:A,0 = I want it to look at the entire column A (to match), and 0 indicates I want that exact value
Now the VLOOKUP
A:B,2,0 = I want to look at the first two columns A (Scaled Score) and B (Matching Percentile).

SCALED SCORE DESCRIPTOR EXAMPLE

In this example, I want F6 to automatically fill the percentile that matches the Scaled Score in C6. So I use this formula:

=IF(ISNUMBER(MATCH(C6,‘SCALED SCORES‘!A:A,0)),VLOOKUP(C6,‘SCALED SCORES‘!A:B,3,0),“”)

C6 = The cell I want to match
Scaled Scores= The tab I want to pull the data from
A:A,0 = I want it to look at the entire column A (to match), and 0 indicates I want that exact value
Now the VLOOKUP
A:C,3,0 = I want to look at the first three columns A (Scaled Score), B (Matching Percentile), and C (Descriptor). I want the third value and 0 = False.

Page updated

Report abuse