Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Alexandra dan
 | Femeie
25 ani
Bucuresti
cauta Barbat
28 - 66 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
When the end user has to add new items to a drop down list, you usually make a data validation that also includes some blank cells in the range, for the new names.
The problem is that it displays blank spaces in the list.
Source with images:
1. Make the list of entries you want to add as options to your drop down list - add them a header / title (as simple as possible)
TITLE
Aaa
Bbb
Ccc
Ddd
2. Select the data you created above including the title of the column, and press CTRL+T to create a new table with that data (make sure My table has headers is checked. Press OK.
3. With the table selected go to a tab called DESIGN (it's under TABLE TOOLS and appears only when you are in a table). On the left of the tab there is a field called Table Name: where is the name of your table, in my example is Table3.
4. Select the cells you want your drop down list in and go to the DATA tab and select Data Validation.
5. Choose List and in the Source filed write the following formula:
=INDIRECT("Table3[TITLE]")
Make sure you replace Table3 with your table name and TITLE with your column name. Then press OK.
6. Now check your drop down lists that they are OK, and write in the next cell right below the table you created; in my example it would be in the next cell after Ddd, like this:
TITLE
Aaa
Bbb
Ccc
Ddd
Eee
The table automatically expands to include that cell and your dropdown list will also include the new entry in the table.
IMPORTANT! Always write the next entry right below the previous one and make sure the table includes it - to make sure you can leave the table colored so you can easily see when it expands.
Modificat de TRaP (acum 5 ani)
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2228
|
|
key words: excel dynamic dropdown
I recently watched a video by Excel Off the Grid YT channel with even better ways to do it.
Using INDIRECT method above was 2nd on their list, because using it you would have to explicitly name the Table and Column, so if you would decide to change the column name in Table3 from TITLE to say Column1, data validation would stop working completely as it would stop finding the column name you explicitly named in INDIRECT.
The 3rd method in their list and the best way to do dynamic dropdown lists in any Excel version is by using a Table and a Named Range.
1. Create table with the desired dropdown list items, for the sake of the example: Table3 with TITLE
2. Create a named range using that table column, say I name it myList and use =Table3[TITLE] as the range
3. Create data validation using =myList
Now even if I change the column name to LIST and the Table name to tbList, the myList named range will automatically change to =tbList[LIST]. I found no downside for this.
The 4th method on their list was a Spill method, which works great for Excel 2021 and Excel 365 only, unfortunately. You would first have to create a Spill list in a range of cells by using the formula =Table3[TITLE] in a cell. Then in Data validation you would refer to that cell and add a # character after it, like this =$F$2#
Source:
_______________________________________
|
|
| pus acum 7 luni |
|