Dynamic Ranges and Blanks

Hello All,

thank you in advance for your respones. I am having some trouble with some forumals and dynamic ranges.


My goal is to return the First, Second and Third highest text values in a dynamic range. (The dyanmic range is a list that is constantly being added to, however, sometimes it will be blank)

Everything works except the moment i skip a line in the dynamic range all values go NA


For for example

C1: =INDEX(vendor,MODE(MATCH(vendor,vendor,0)))
C2: {=INDEX(vendor,MODE(IF(COUNTIF(C$1:C1,vendor)=0,MATCH(vendor,vendor,0))))}
C3: {=INDEX(vendor,MODE(IF(COUNTIF(C$1:C2,vendor)=0,MATCH(vendor,vendor,0))))}

This is the set that returns the first second and third highest values

Column A is my dynamic range with the dynamic named range:

=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B$1:$B$100),1)

the name is vendor.

This works except for if i leave a blank space in column A it all stops working.

What am i doing wrong?

Thanks,

Dan

Here is a sample book. If you delete anything within A1:A33 it will turn to NA