Blank Cells and Dropdown List

mvita

New Member
Joined
Jan 14, 2005
Messages
24
Hello All,
I tried to look through this maze but failed to get an answer after 2 hours of searching.
My "problem".
I have a Dropdown list defined of say 120 cells (in a column). The cells have been given a Name and are sorted alpha A-->Z.
Depending on the location where the file is used, sometimes only 80 cells are populated.
When a user clicks the cell where the validation list is ON, the user sees the blank cells first.
Q. Is there any way to show the populated cell (1st cell) to show when the dropdown arrow is clicked?
PLEASE HELP.
Thanks a lot everyone.
Kind regards
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Aladin, Thanks for the Quick response.
The list range is B4:B133
Name of list is "perslist"
Name of Worksheet is "masterdata"
Hope this helps.
Kind regards.
A
 
Upvote 0
mvita said:
Aladin, Thanks for the Quick response.
The list range is B4:B133
Name of list is "perslist"
Name of Worksheet is "masterdata"
Hope this helps.
Kind regards.
A

Activate Insert|Name|Define.
Select perslist.
Change what you see in the Refers to box to:

=masterdata!$B$4:INDEX(masterdata!$B:$B,MATCH(REPT("z",255),masterdata!$B:$B))

Click OK.
 
Upvote 0
Aladin,
1000 Thanks, works perfect, I used $B$4:$B$133 instead of the whole column B:B
Will try to make my own in future.
Again, THANK YOU.
Kind regards.
A
 
Upvote 0

Forum statistics

Threads
1,203,528
Messages
6,055,928
Members
444,835
Latest member
Jonaskr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top