I was trying to write a formula that will enable me to chose a school name (from a data validation dropdown list) and have 4 years of test scores come up. I wrote the following formula which works really well except for 1 small problem. It sorts the data for a given school in ascending order. I do not know how to write the formula so that it pays attention to order of the original data (which are correct by year). Here is the formula I used
{=IF(COUNTIF($B$2:$B$45,$G4)>=COLUMNS($G$3:$H$3),SMALL(IF($G4=$B$2:$B$45,$C$2:$C$45,""),COLUMNS($H$3:H3)),"")}
My data are arranged with the year in the a column, name of school in b column, score in c column. The schools have 4 years worth of data.
Thanks!
{=IF(COUNTIF($B$2:$B$45,$G4)>=COLUMNS($G$3:$H$3),SMALL(IF($G4=$B$2:$B$45,$C$2:$C$45,""),COLUMNS($H$3:H3)),"")}
My data are arranged with the year in the a column, name of school in b column, score in c column. The schools have 4 years worth of data.
Thanks!
