Dynamically Updating Data Validation Drop Down Menu/List

[sgkessel]

This is probably easy for your Excel genius'
I have made a data validation drop down list that SHOULD update for when more values are entered into the column being referenced with an offset function

=OFFSET(Data!$AR$6,0,0,COUNTA(Data!$AR:$AR)-5)

the problem I am having is that Column AR has either text or blank/space
not EMPTY CELL which I believe is what this formula above is looking for...

I would like my drop down menu to only show text from column AR and dynamically update as "" turns into Text.

PLEASE HELP... I'm sooooo close! Thanks!!!

 

[cyrilbrd]

Hi, not sure if this will for you.
Without VBA:
Considering that your data in AR6 down has a Named range called "lost"

in AS5 type:
=IFERROR(INDEX(lost,MATCH(0,IF(MAX(NOT(COUNTIF($AS$4:AS4,lost))
*(COUNTIF(lost,">"&lost)+1))=(COUNTIF(lost,">"&lost)+1),0,1),0)),"")
Ctrl + Shift + Enter, not just enter, copied down till needed.
in AT6 type:
=IF(AS6=AS4,"",AS6)

Your formula for the data validation would be:
=OFFSET(Data!$AT$6,0,0,COUNTA(Data!$AT:$AT)-5)

Would that work?

 

[T. Valko]

This is probably easy for your Excel genius'
I have made a data validation drop down list that SHOULD update for when more values are entered into the column being referenced with an offset function

=OFFSET(Data!$AR$6,0,0,COUNTA(Data!$AR:$AR)-5)

the problem I am having is that Column AR has either text or blank/space
not EMPTY CELL which I believe is what this formula above is looking for...

I would like my drop down menu to only show text from column AR and dynamically update as "" turns into Text.

PLEASE HELP... I'm sooooo close! Thanks!!!

Maybe this...

=OFFSET(Data!$AR$6,0,0,COUNTIF(Data!$AR$6:$AR$65536,"?*"))

Adjust for a reasonable end of range AR65536.

 

[sgkessel]

maybe this...

=offset(data!$ar$6,0,0,countif(data!$ar$6:$ar$65536,"?*"))

adjust for a reasonable end of range ar65536.

score!!!
Works perfect!!!
Thanks!!!!

 

[T. Valko]

score!!!
Works perfect!!!
Thanks!!!!

You're welcome. Thanks for the feedback!