UKC

Once again I am back to the font of all Excel knowledge

I need some help with drop down menus on certain columns.

I enter supplier details into column A and want to be able to select a supplier from a drop down menu and filter by suppliers for monthly reports.

At the moment it will allow me to create a drop down from data by using the data validation tab and select the source cells but will not allow me to enter a new supplier direct into a cell in column A and then be able to select that later if I need to enter another order.

Is it a case of having to enter all the suppliers first then creating the drop down?

In reply to cfer:

So you are trying to use Col A both for populating the list and also as the target?
I would suggest changing the source list to be another column (on another sheet).
Then if you need to add any new records you can just add there as needed.

In reply to cfer:

Hi cfer


In a nutshell, yes. In my experience of using these things I have setup the list that you can select from and any time I needed to add something to this list added to it. A tip, so you don't have to update the list range every time if you add rows in the middle of the current list and then enter the new one the validation will automatically update.

There may be someone along shortly to tell you about a quicker, better way to do it. I've always found data validation and lists a bit clunky in Excel which probably means I'm going the long way around.

Hopefully that helps,
Chris

In reply to dissonance:

Yes, say I have 10 different suppliers in column A

Supplier
A
B
C
D
E
F
G
H
I
J

The next time I enter a supplier I would like to be able to either select one of the previous 10 or add a new one, (K) that can then be selected further on in the spreadsheet

In reply to cfer:

I figured it out, just need to select the range of cells I want then also another ten extra empty lines then will do as Pebble Pusher said and insert rows to include the data in future rows.

Thanks everyone

In reply to cfer:

"Named Ranges"

1) Make a hidden sheet somewhere with a list of suppliers on
2) Assign a named range to that
3) In the validation list for the drop down, assign the Named Range
4) you can then add new suppliers to the Named Range, and the validation list will update accordinly.

Pm me if you want a bit more detail on creating named ranges (not sure what your excel powers are like!!)

In reply to ThunderCat:

That's it! I knew there was a better way to do it, I've used named ranges for other things but never thought to apply it to data validation.

You learn something every day!

Thanks
Chris

In reply to cfer:

I found it best to make lists in a different worksheet so you can have suppliers and anything else you might want listed in a drop down box. Create your list then go "Insert" - "Name" - "Define", you can then make a name of the list (Suppliers) and select the range of cells to use. Then when you do the "Data" - "Validation" thing, select "List" and put "=Suppliers" into the range selection (minus the quotes) and hey presto!

When you first define the list, you can have blank cells at the bottom for adding new ones, or simply go back to the "Define" stage and extend the range when more are added.

In reply to richyfenn:
or you can make the named range dynamic, using the offset command in the box in the Name Manager where you state the range limits.Something like

=OFFSET ($a$1, 0,0,counta(A:A),rangewidth)

This will assign the name to a block with cell A1 at top left, with as many rows as are occupied in column A, and of width "rangewidth"

This way, the range expands automatically as you add new suppliers. I do this a lot where ranges are going to need to expand as new data is added.

In reply to mbh:

^^^
Wot he said.
Sounds complicated initially but once you get the hang of it it's by far the simplest and most flexible way to maintain dynamic lists.

In reply to mbh:





MMmmm....I'm trying this tomorrow.

Usually I just insert the new supplier 'in the middle' of the range, so tha t the range changes because adding it to the end doesn't actually add it to the range...but this will definately be easier for stuff I'm creating for other people.

nice one!

In reply to mbh:

I wish I'd known this trick!

In reply to cfer:

Not quite as elegant as using named ranges but it's an alternative. Rather than applying any data validation to your cells, just type in the name of the suppliers.

When you need to enter one you've already typed in, hold down ALT and press DOWN. It'll bring up a list of all the unique values in that column.

It only lists text values though, it won't list (for example) "1234".

This works in 97, which we're still stuck on in work, but I'm pretty sure it works in all later versions too.