• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation with visible names only

S P P

Member
Is there any formula that searches with data validation, only the names that appear in the description worksheet
 

Attachments

  • Data validation with visible names only.xlsx
    12.5 KB · Views: 10
See attached. Feel free to ask questions.
 

Attachments

  • Chandoo53612Data validation with visible names only.xlsx
    13.3 KB · Views: 4
p45cal

Thanks again for the help

When adding a row to table1 Sheet1 types a new description appears in table2, but does not appear in validation
 
Try,

1] In Sheet "Description (table2) ", revised B4 array (CSE) formula copied down to B100

Remark : Just add a &"" as per highlighted below.

{=IFERROR(INDEX(Tabela1[Description],MATCH(0,COUNTIF($B$3:B3,Tabela1[Description]&""),0)),"")}

>> become >>

84324

2] Add new description in table2, cell B10, e.g. "AAAA"

>> become >>

84325

3] Then, go to Sheet "Plan 1 (table1)", you will find "AAAA" appeared in the new validation list last position.

>> as show below >>

84326
 

Attachments

  • Data validation with visible names only (BY).xlsx
    15.1 KB · Views: 5
p45cal

Thanks again for the help

When adding a row to table1 Sheet1 types a new description appears in table2, but does not appear in validation
Sorry!
I should have made Table2 include a number of blank rows (the formula will extend automatically)
See attached.
 

Attachments

  • Chandoo53612Data validation with visible names only.xlsx
    14.2 KB · Views: 1
bosco_yip

Thank you for your participation

Your suggestion was the same as my worksheet.

What I want is for the validation to appear only for registered users without a scroll bar.

p45cal is on track as I intend.
 
I'm using this formula, but it's not working either

=OFFSET(Description!$B$4;0;0;COUNT(Description!$B:$B);-1)
 
Try the attached. Conditional formatting is superfluous because anything new added will also be added to the DV!
 

Attachments

  • Chandoo53612Data validation with visible names only.xlsx
    15 KB · Views: 3
p45cal

There's something wrong I open the spreadsheet and maybe it doesn't work for me.

It's better to leave this too much work
 
Back
Top