Update a Dropdown List automatically

Update a Dropdown List automatically
Last Updated on 27/04/2024
Reading time: 3 minutes

How do you update a dropdown list when you add data to your Table?

  1. Use the Table Reference Table[column]

    You can use such reference, but you must embed the references inside an INDIRECT function.

  2. Or, .... do nothing

    That's amazing. Even if you don't use Table Reference, the dropdown list will know that your data comes from a Table.

A common problem when you update your source

  1. You have created a dropdown list in F1 links to the range D2:D5
Dropdown list in Excel
  1. Then you add a new name in D6.
  2. Unfortunately, the name doesn't appear in the dropdown list
The dropdown list hasn't been updated

The reason is simple.

  • The range of cells of your dropdown list is still D2:D5 (and it should be D2:D6).
Data Validation Source
  • To achieve a dynamic dropdown list, utilizing a Table is essential.
  • Tables provide dynamic ranges for data, ensuring flexibility and efficiency in dropdown list creation.

Create a dropdown list with Table References

Now, you insert your data into a Table

  1. Select your data source
  2. Activate the tool Insert > Table
Menu Insert Table

The result is this

Add your data in a Table

Now, you want to use the Table's references as the source of your dropdown list. But there is a problem 😱😱😱

Excel doesn't accept Tables' references as a source in any dialog box, such as data validation, custom conditional formatting rule, etc.

Write the reference Table as Source for the dropdown list

And you have this message

Excel error when the source is a reference table

But there is a trick to avoid this problem

The trick is to write your Table's references in the INDIRECT function 😉

  1. Write the function name =INDIRECT(
  2. Add a double quote (")
  3. Then, the Table's references
  4. Another time, add a double quote to close the string.
  5. Close the parentheses

=INDIRECT("Table1[Name]")

INDIRECT function to write the table references

And now your data source is dynamic 😍😍😍

How to create a dynamic dropdown list

"Invisible" Dynamic reference

Here, it's tricky. Because

  1. Our data for the dropdown list is still inside a Table
  2. Open the dialog box of the dropdown list
  3. Select the source field
  4. Click on the Table column's header to select this column's whole data.
  5. The references look statics, but that's a trap !!!!!!!
No Table reference with this technique

Now, when I add a new name in D6, what do you think will be going on with the dropdown list?

  1. I will have only 4 names?
  2. Or will the new one be added to the dropdown list?

The answer is 2.

The new name is added in the list automatically

The name will be added to the dropdown list because I have selected my data from the table header. Excel knows the source is inside a Table even if It didn't write the source as a Table's reference. 😉👍

When you check the source field of the dropdown list, you can see that the reference has been updated accordingly.

The dropdown source has been updated

1 Comment

  1. zp
    30/05/2021 @ 04:27

    good job bro

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Update a Dropdown List automatically

Reading time: 3 minutes
Last Updated on 27/04/2024

How do you update a dropdown list when you add data to your Table?

  1. Use the Table Reference Table[column]

    You can use such reference, but you must embed the references inside an INDIRECT function.

  2. Or, .... do nothing

    That's amazing. Even if you don't use Table Reference, the dropdown list will know that your data comes from a Table.

A common problem when you update your source

  1. You have created a dropdown list in F1 links to the range D2:D5
Dropdown list in Excel
  1. Then you add a new name in D6.
  2. Unfortunately, the name doesn't appear in the dropdown list
The dropdown list hasn't been updated

The reason is simple.

  • The range of cells of your dropdown list is still D2:D5 (and it should be D2:D6).
Data Validation Source
  • To achieve a dynamic dropdown list, utilizing a Table is essential.
  • Tables provide dynamic ranges for data, ensuring flexibility and efficiency in dropdown list creation.

Create a dropdown list with Table References

Now, you insert your data into a Table

  1. Select your data source
  2. Activate the tool Insert > Table
Menu Insert Table

The result is this

Add your data in a Table

Now, you want to use the Table's references as the source of your dropdown list. But there is a problem 😱😱😱

Excel doesn't accept Tables' references as a source in any dialog box, such as data validation, custom conditional formatting rule, etc.

Write the reference Table as Source for the dropdown list

And you have this message

Excel error when the source is a reference table

But there is a trick to avoid this problem

The trick is to write your Table's references in the INDIRECT function 😉

  1. Write the function name =INDIRECT(
  2. Add a double quote (")
  3. Then, the Table's references
  4. Another time, add a double quote to close the string.
  5. Close the parentheses

=INDIRECT("Table1[Name]")

INDIRECT function to write the table references

And now your data source is dynamic 😍😍😍

How to create a dynamic dropdown list

"Invisible" Dynamic reference

Here, it's tricky. Because

  1. Our data for the dropdown list is still inside a Table
  2. Open the dialog box of the dropdown list
  3. Select the source field
  4. Click on the Table column's header to select this column's whole data.
  5. The references look statics, but that's a trap !!!!!!!
No Table reference with this technique

Now, when I add a new name in D6, what do you think will be going on with the dropdown list?

  1. I will have only 4 names?
  2. Or will the new one be added to the dropdown list?

The answer is 2.

The new name is added in the list automatically

The name will be added to the dropdown list because I have selected my data from the table header. Excel knows the source is inside a Table even if It didn't write the source as a Table's reference. 😉👍

When you check the source field of the dropdown list, you can see that the reference has been updated accordingly.

The dropdown source has been updated

1 Comment

  1. zp
    30/05/2021 @ 04:27

    good job bro

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *