Combo Box in Excel

[BetterBeef]

I am used combo boxes in the past with Access but am not familiar with how they work in Excel. I have two lists in Column E and Column F of a worksheet called "Lists". I have a combo box in cell E2 of a worksheet called, "2009", that I would like to populate with the two lists. I would like the combo box to work like a regular cell that uses the Validate... option. The reason I don't like the Validate... option is because I cannot have two columns show up at the same time, which will assist the user to select the correct response. After the selection is made, I want the information in column E in worksheet "Lists" to be stored.

I also want to say that I would like this "combo" box in E2 of '2009' to repeat as a new row is created much like it would in a list. I'm hoping that by selecting the list feature in excel, this box will repeat as needed.

 

[Fenrirshowl]

If I understand correctly, you want to validate a cell such that it appears to be a combobox from Access with two columns as the source.

Could you not simply create a new column in Lists which contains info from both Col E and Col F and use this as the list source for the validated cell, i.e. G1 = E1 & F1?

Appearance of the validated cell: Working on the sheet "Lists". You could even set use LEN and REPT functions to set up a divider. i.e. if E1 = "A" and F1 = "1" then you could have G1 = =E1 & REPT(" ",10-LEN(E1))&"|"&REPT(" ",10-LEN(E1))&F1
to make it appear like separate information.

If you need to return the information from column E only (if they are factors say) then you can use MATCH and INDEX to extract the required info in another (possibly hidden) column, if you so wish.

Fen

 

[BetterBeef]

Perhaps I did not give enough detail to my request. For those of us who use Access, when a user uses a drop down combo box, sometimes it is possible to allow the user to "see" two columns of data. The first column is the information that gets returned to the cell. The second column is additional information that makes it easier for the user to make the correct selection.

For example, if the combo box was referencing days of the week, a user could see all days of the week by abbreviation in the first column (Mon, Tues, Wed, etc...) and in the second column that pops up the days spelled out (Monday, Tuesday Wednesday, etc...). In this example I would want the abbreviation to be returned to the cell, but not the written out day.

This is done in Access all the time, and I was just wondering if it is possible in Excel. I'm not interested in merging both columns to be returned by the combo box. I just want to return the abbreviation. (Obviously, I am using abbreviations more complex than days of the week. This is just an example).

I appreciate your help and patience.

Thanks.

 

[Fenrirshowl]

To my knowledge it can't be done.

The best you could try for is to validate a cell with the drop down list that you want to reference for the user to see, and then use that value to extract the abbreviation you want your spreadsheet to use (for which I would use MATCH and INDEX functions).

i.e. cell validated to be E1:E10 say on lists (assume it is D1 one Sheet1)

then E1 in Sheet1 =INDEX(List!F1:F10,MATCH(D1,List!E1:E10,0))

Fen