Creating Dynamic Drop-Down

Spreadsheet with Drop-Downs.xlsm

I am having difficulty creating a dynamic drop-down on one tab based on values on another tab (which could range from 0 - 44). Just using the range of the entire column, the drop-down, when clicked, open at the first blank below the last value in the list.

I am using MS Excel 2016 and apparently "Unique" isn't available. I've tried using "Offset" but it doesn't work. I've tried using "Indirect" w/ the values in a table but found that when the table expands on one occasion but then later has null values in the expanded list the drop-down includes the blank values in the drop-down.

I could use Indirect with the table for the column if I could figure out how to dynamically change the size of a table in a macro.

Any assistance that one can supply would be greatly appreciated.Spreadsheet with Drop-Downs.xlsm

Torachan,

I tried this and it works but if the table is expanded for more entries, and then subsequently the values in that table are reduced, there are blanks in the drop-down below the valid entries. Is there a way to dynamically change the column table length to match the number of entries in that column table so that these blanks do not show in the drop-down?

Jeff

My apologies for not properly attaching the spreadsheet. I am new to this forum.

I tried using that formula in the drop-down, adjusting "$B$2" to be the first entry in the column, but the drop-down still opened with a blank with the entries above. And, there are blanks in the drop-down for the entire column where the data is housed with no data in those cells. Besides using a structured table is there a way so that only non-blank values in the column appear in the drop-down and the drop-down starts at the top value in the data column that it accesses?

Jeff

Thank you menem!

Torachan,

Is there a way to dynamically properly delete entries in the table, as in a macro?

Jeff