Drop down lists

  Craig.m 14:24 19 Jan 06
Locked

In Excel I can make a drop down list using Data and Validation. This is great most of the time, I am now requiring a drop down that not only lets you lelect from a predefined list but also allows you to type straight into the cell a value that may not be in the list.

I am not too bothered if the value is included in the list for future use, but it would be nice.

Anyone who can tell me in fairly simple terms if and how this could be achieved. I take the validation route is not really the one to take as it is validating what you put in the cell by means of a list and this is contrary to its purpose.

I have tried the toolbox function and got a blank drop down box but I can't figure how to populate it with a list. I seem to manage it in Access but this doesn't seem the same when I go into properties. The other thing with using the toolbox is that the list box is not actually a cell, so is getting the data in that box into the cell is a little beyond my ken.

Craig

  VoG II 15:49 19 Jan 06

View, Toolbars, Forms. Insert a Combobox.

Right click it, select Format Control.

Input Range is the range where your list is stored.

Linked cell is the cell where the chosen value will be written. This value is the index number of the selection. Thus if you had your list in A1:A3 and the linked cell is A5, the foillowing formula will give the linked value.

=OFFSET(A1,A5-1,0)

  Craig.m 16:57 19 Jan 06

Thanks for the response, a few issues there, firstly I created a combo box but format control does not have any options where I could dump a formula. The second is the formula - what limits the range to A1:A3 when you have put A1:A5? What would be the formula for a range of A1:A8 with the result in B1?

Craig

  VoG II 16:59 19 Jan 06

You would have to 'dump' the formula in another cell.

what limits the range to A1:A3 when you have put A1:A5? - don't understand the question.

=OFFSET(A1,B1-1,0)

  Craig.m 17:36 01 Mar 06

What I was asking was that your formula was OFFSET(A1,A5-1,0), I don't understand where there definition of the cells A1 to A3 is, all I see is A1,A5.

Basically I like to know what it is that I am doing but to me I don't see what that formula does as I can't see where there constraints of A1 to A3 are, I see the reference to A1 and A5, I know A5 is supposed to be the repository of what is chosen from A1 to A3 but what limits the list to A1 to A3, I see no mention of A3 in there?

  VoG II 17:49 01 Mar 06

A1 to A3 (or whatever you define the range as) are the cells where the values displayed in your combobox are stored.

  Craig.m 18:07 01 Mar 06

I cannot find anywhere in format control that has an input for the range, went into properties and nothing there either.

The formula you gave me originally - where does that go?

Think it is easier to do a drop down box in Access, leave these type of functions to a database as it never seems to be quite so easy in Excel

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Method Studios' title sequence for BBC series Taboo is truly unsettling

Best Pages for iOS tips | How to use Pages for iPad & iPhone: 6 simple tips to get more out of…