Share via

Data validation list from table using if statement

Anonymous
2020-02-20T11:20:12+00:00

I have this formula in data validation source:

=IF((OR(A2="Device1",A2="Device2")),Lists!$E$2:$E$7))

This works, but i want to use table name instead of range, because it will be dynamic table.

Is it possible to use Table name instead ofLists!$E$2:$E$7?

Thanks in advance

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Lz._ 38,106 Reputation points Volunteer Moderator
    2020-02-20T12:02:14+00:00

    Hi again Marko

    Your Table can be on any sheet no problem. Input either Device1 or Device2 in A2, then put in place your Data Validation with the formula you posted and this will work...

    1 person found this answer helpful.
    0 comments
Answer accepted by question author
  1. Lz._ 38,106 Reputation points Volunteer Moderator
    2020-02-20T11:31:20+00:00

    Hi Marko

    Is it possible to use Table name instead ofLists!$E$2:$E$7?

    Not directly with Data Validation. Assuming your Table is named myTable and your list items are in column [Foo] of that Table

    • Define name i.e. myList with formula: =Table1[Foo]
    • then use myList instead of Lists!$E$2:$E$7

    Nice day...

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-02-20T11:53:12+00:00

    I have already tried this way, but i got message

    =IF((OR(A2="Device1",A2="Device2")),myList))

    What can be a problem? Is it necessary to put in a quotes, or something like that...

    The column [Foo] is on another sheet, if that is imoprtant.

    0 comments
  2. Anonymous
    2020-02-20T12:10:05+00:00

    Great job. Thanks

    0 comments
  3. Lz._ 38,106 Reputation points Volunteer Moderator
    2020-02-20T13:02:37+00:00

    You're welcome. Glad I could help & Thanks for posting back

    You might be interested reading Data Validation list uniques, auto-sort, no blanks ...

    0 comments