drop down list

[dan03uk]

Im currently creating a drop down list for a range of cells. The range of cells I have chosen use the formula =IF($G$2=A3,A3,NA()), thr problem is that when they are showing NA they still appear in the drop down list is there any way of stopping this from heppening.

Any help would be much appreciated

 

[JasonC71]

It's a little unclear about what you are trying to do...but..if i've read correctly..try the formula:

=IF($G$2=A3,A3,"")

Instead of:

=IF($G$2=A3,A3,NA())

to populate your range of cells.

 

[nancymk]

You could do a pivot table on your list and exclude the NAs. And then use the pivot table for your list, by putting that field on the left side of the pivot table. You would just have to refresh your pivot table though, if you changed anything. Though I think there is a setting to automatically refresh?

 

[shemayisroel]

Perhaps you could change your approach by using the below.

Assuming your list is in Column A.

The below will update your drop down list by changing your range to.

=$A$1:INDEX($A:$A,MATCH("ZZZ",$A:$A))

 

[dan03uk]

Cheers for the help guys.

 

[shemayisroel]

Cheers for the help guys.

No probs, which approach did you end up using?