For Data Validation - Settings - List Data - Source How to restrict Table Header in DropDown List??

Question

Is it possible to restrict Table Header in dropdowns when whole Column is selected as Data Validation List Source?

Or do I have to select Data Cells for Source?

If yes, for large Table, is there alternative to the following:

1. select first Data cell of Table
2. use two Keyboard shortcuts Ctrl-DownArrow if Blanks exist after last Non-Blank

Not only is there more keystrokes described above, but also for associated functions. Example is VLookUp range will also require selecting Data Cells.

If Data Validation can intelligently ignore Header Rows, I for one enjoy less keystroke and wrist wear

Thanks for consideration!

Sunny

Answer 1

You have to select the data cells only, but that's easy in a table.

If you hover the mouse on a column name (A,B,C,etc.) the cursor changes into a down arrow and if you left click you select the whole column.

If you hover the mouse on a header name in a table, the cursor changes also into a down arrow and if you left click you select the data in the table only.

Andreas.

Answer 2

You have to select the data cells only, but that's easy in a table.

If you hover the mouse on a column name (A,B,C,etc.) the cursor changes into a down arrow and if you left click you select the whole column.

If you hover the mouse on a header name in a table, the cursor changes also into a down arrow and if you left click you select the data in the table only.

Andreas.

Thanks,

Data Selection Method rather than Column

• I interpret you meant bottom border; which needs practise because bottom border is typically thin
• Hover on header name is much easier but didn't produce the down arrow in my case; should this be feature request?
• if the bottom of Table is blank, Vlookup function will fail (#N/A error) if selection is Data vs Column.

So, is the following the proper ? :

1. Data Validation List to use Data Selection
2. Vlookup, if bottom of Table is blank, to use Column

Thanks Ahead !

Answer 3

Hover on header name is much easier but didn't produce the down arrow in my case; should this be feature request?

In this case you don't have a table.

Create and format tables - Office Support

Take a look:

And there is no need to have blank rows in a table, formulas used in a table are filled down automatically if new data is added.

Andreas.

Answer 4

Thanks for more illustration.

However, my user experience is much less fluid; as shown by following.

Would you know why the down arrow don't show at first hover?

I will gladly eMail you the Workbook which has a small table with

1. 6 x 6 cells
2. one DropDown
3. 6 Vlookup formulas