The defined names have twice as many items for example my list that applies to the workbook and my list that applies to the new tab.

Tables are available in Excel versions 2007, 2010, and 2011.

Please note that Table1 is the name of the Table created in the step above.

It will return the header row of the Table, which well use in the next step.

This will bring up the New Name dialog box that looks like the Edit Name screen-shot shown above.

As you can see in the picture below, the Vegetables item list has two blank lines, and the Other Stuff item list has one blank line.

Choose List and enter the following formula myItem, then click OK.

To create a new row in the Table with Data Validation press the Tab key while the active cell is the last column of the last row.

Should your data have blank rows in the middle of the column, the drop-down wont have all the items listed yet show blank cells in the list.

At least youll get all the data, even if it does have some blanks in the list.

Normally I would place the reference Table on a different worksheet.

Drop Down Table In Excel Download This File

Then click on the link download this file and you will get a working file downloaded to your computer.

It is a great feature since it allows either selecting existing values or entering new ones.

I have not been able to figure out a way of eliminating duplicates in the drop-down.

I have a problem with the dependent lists, and I see its also in the sample excel file youve distributed, and would really appreciate your assistance on this matter.

For example, in your excel file, if I change A2 from fruits to vegetables, B2 still displays Banannas.

Only when I actually click on the drop-down menu in B2 do I see the vegetables.

Drop Down Table In Excel Update The Displayed

Is there any way to update the displayed value in B2 once I switch items in A2.

If you want MyFormula to recognize when there is a blank cell in A2, then you should modify the formula as follows.

However I believe that there is some event-driven behavior with the Data Validation that sometime interferes.

In that case, the choice in B2 now no longer reflects the choice in A2.

But it doesnt help with the above scenario, where you in fact DID make a selection from A2, then made a valid selection from B2, but then subseqently changed your selection in A2 making the data in B2 out of context.

For example: I have a range 1 to 30 to indicate discounts but any sales reps can grant up to 15 (no password required).

Manager1 password can allow additional discount to 20 and Manage2 password allows the full range to 30.

Drop Down Table In Excel For Mac