How to create a drop down list from horizontal source data

A

acounter2

Board Regular
Joined
Jan 16, 2008
Messages
57
How to create a selection list (drop down menu) which obtains values from defined named horizontal area? (In fact consisting of a table column names and headers on the next worksheet)
Forms->drop down list seems to work only on data colum and not horizontally.

I'm trying to create a filtered table with HLOOKUP function with data extraction from full table. Where lookup is based on drop down list combined of names in full table first row on next worksheet(being column header on the same time).

The result of first selection list above will be linked to the cell used for HLOOKUP.

I hope it sounds logical and will not turn into cross reference :)
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
I almost managed to find a temporary workaround, which may be difficult to maintain and either doesn't look elegant.

1.I gave a named to full table worksheet header row used for hlookup and on the selective table view page.
2. used array transponse function to create the headers listed in column view to selective table worksheet.
3. I used Data validation function dropdown list function, which is limited a) to column list b) must be on the same worksheet (can't be hidden to empty nor full table worksheet)

At first glance everything works but even if there doesn't appear any conflict, updating table will be an headache.

I hope experienced Excel users can suggest something more wise :)
 
Upvote 0
I created a Forms>ComboBox and named it "DropDown", as i'm assuming thats the dropdown box you're referring to.

I named the horizontal range "RngA"

IF my assumptions are correct, the following should work for you.

Code: 
Sub DropDown()

    With ActiveSheet.DropDowns("Dropdown")
        
        .ListFillRange = ""
        .List = Application.WorksheetFunction.Transpose(Range("RngA"))
            
    End With

End Sub


If you're just using an in-cell drop down through data>validation List: just selecting the range will work the same.
 
Last edited:
Upvote 0
Thanks.
I have Excel 2003.

When I pick from Forms Toolbar a Combo Box, there appears a big box with down arrow. I can't figure out how do I name this object "DropDown"
It doesn't seem to have a name property.
With rightclick I picked an Assign Macro and created one but I'm not sure if macro really is linked to box - likely not until dropdown box doesn't have expected name.

2. And Format control doesn't need any data entry - control input range is assigned by macro I guess?
Except Cell link which I have to set according to hlookup when button works.
 
Upvote 0
EB08 said:
If you're just using an in-cell drop down through data>validation List: just selecting the range will work the same.
Click to expand...

Excel 2003 Data validation seems to work only with vertical range and on the same worksheet.
 
Upvote 0
I would say if you have no issues using data validation; thats your easiest option. What exactly are you choosing that won't work with a horizontal range? If i go to Data>Validation:Allow=List, then choose or drag a horizontal range it works perfectly.

If you need a range from a different sheet, select the range on the sheet you need and use the 'name box' (near top left, right above column header "A") to give it a name...ie 'TheList'. Then on the sheet/cell of your choice, choose data>validation: Allow=List, then type in =TheList in Source box.
 
Upvote 0
EB08 said:
I would say if you have no issues using data validation; thats your easiest option. What exactly are you choosing that won't work with a horizontal range? If i go to Data>Validation:Allow=List, then choose or drag a horizontal range it works perfectly.
Click to expand...
You are right - it works with horizontal range when I select cells but when I name the range, it doesn't work, instead displays the Range name only

EB08 said:
If you need a range from a different sheet, select the range on the sheet you need and use the 'name box' (near top left, right above column header "A") to give it a name...ie 'TheList'. Then on the sheet/cell of your choice, choose data>validation: Allow=List, then type in =TheList in Source box.
Click to expand...
Data Validation doesn't allow to pick cells from other worksheet and TheList doesn't work in the same manner as from the same worksheet, just displays the own name in the list :(
 
Upvote 0
in regards to TheList; you just forgot to type in the "=" before TheList. = will tell it that its a named range, just typing TheList will treat it as text. You're very close, just a couple of small details off...but this definitely works in 2003.


I just did a quick search on a familiar site and it has a visual explanation of what we're discussing here...

http://www.contextures.com/xlDataVal01.html
 
Last edited:
Upvote 0
I must continue tomorrow but just a short test -
yes, missing = did the miracle and Data validation seems to work across worksheet using Names as I expected. Thanks!
:p
 
Upvote 0
As told - I have almost done :) Thanks going to EB08!

But it appears that Data validation drop down list with source from horizontal range displays the drop down list in the order as these appear in range, means not alphabetically. Not a problem itself, but HLOOKUP doesn't understand it and looks up the column according to imaginary alphabetical drop down list order.
That means some hlookup column results are shifted and taken from wrong column. When I sorted base table alphabetically the hlookup results were correct also. But base table will be changed from time to time and so occasional mistakes may occur.

Is there any easy fix to Data validation dropdown list and hlookup to use the same cell reference?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
227
Jeffrey Mahoney
Jeffrey Mahoney
D
  • Solved
Drop down list to display only grouped items
2
Replies
11
Views
218
Peter_SSs
Peter_SSs
C
  • Question
Dependent Drop Down Help, Excel 2010
Replies
5
Views
137
Akuini
Akuini
M
  • Question
Auto Sort rows based on a selection from a drop down menu
Replies
4
Views
52
Joe4
Joe4
T
  • Question
Show different values to drop down box
Replies
1
Views
189
Scott Huish
Scott Huish

Forum statistics

Threads
1,214,911
Messages
6,122,192
Members
449,072
Latest member
DW Draft

Share this page

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