Excel 2007 : 2 drop-down lists - show only available

[Piticu]

Hi,I'm new here.I have a table with bots(Name,Size,Price,Category.....) And i have to do a Purchase Order.I wanna do 2 drop-down lists.The first with the names and the second with the sizez,but only sizes avalaible in the table.How could I do it??I've looked up on the internet the whole day and I didn't manage to do it.Thanks

[jeffreybrown]

Hi & Welcome to the Forum,

Maybe this will help...

http://www.contextures.com/xlDataVal02.html

[Spencer101]

Hi,

Do you mean certain names are only available in certain sizes OR you only want the second drop down to show those sizes that are currently available for each?

If it's the first one then the link in Jeff's reply above has all the information you need.

[Piticu]

Spencer: Yes.I've got something like this:
ADIDAS KANADIA 4 TR GTX M 42
ADIDAS KANADIA 4 TR GTX M 42 1/3
ADIDAS KANADIA 4 TR GTX M 43 1/3
And when the buyer choose from the first list ADIDAS KANADIA 4 TR GTX M in the second i want only those three or more sizes.I don;t want them all
jeffreybrown: I will try your link! Thanks.

[Spencer101]

Then yes, the "dependent drop down list" approach using =INDIRECT() is what you need.

[Piticu]

Thanks for help,I'll try to do it.I hope I won't meet problems! :D

[Piticu]

I meet a problem .I've got those names too long .For Example: PUMA MOTORINA BALLERINA WNS,ADIDAS LIBRIA WINTER BOOT CP PL W,PUMA EPIC FLIP BEACH JAM
.I dont know how to do those Name Boxes with such kind of names .

[Spencer101]

You cannot use spaces in the range names so either have to combine them as one, PumaMotorinaBallerinaWns or Puma_Motorina_Ballerina_WNS.

Hope that helps.

[Piticu]

it works now,i had to use SUBSTITUTE too but it works.And if i want do it with combo boxes i think i have to write in VBA the code.Am i right?

[Spencer101]

I can see how SUBSTITUTE would be helpful in that. Well done

Not sure about combo boxes I'm afraid. Not my area of expertise at all..

[jeffreybrown]

Yes, if you want to use combo boxes in-lieu of the data validation, you'll need VBA code; however, IMO using data validation is much easier.

[Piticu]

And i've got a last questions :D ...How can i use those names : NIKE AIR* PEGASUS+ 26 (GS/PS) and PUMA 2,9 WN S SEASONAL in tha Name Box.It says that i have to enter a valid reference.

[jeffreybrown]

You can type in Name Manager in Excel Help for further clarification.

Learn about syntax rules for names
The following is a list of syntax rules that you need to be aware of when you create and edit names.

Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
Note You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1.
Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
Name length A name can contain up to 255 characters.
Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.

[Piticu]

Jeffrey: I will have to use VBA code,because when i'll finish the Purchase Order and I will have to press button Order, the number of pieces from the list with the table have to decrease.I've got no idea how to do it! :D ..Sorry for double-post!

[jeffreybrown]

Hi Piticu,

I'm not going to be all that available for the next few hours, therefore, my recommendation.

First, start a new thread (with an appropriate title) so you can get the most exposure for this question as possible. With this post count topping 14, not to many people will be looking at this thread.

Second, it will benefit all involved if you attach a sample workbook with what you currently have and what you desire. Data validation may still be a viable option, but we never know until we see your true requiremnts.

If you want to wait I would be happy to look back into this thread, but I can't promising anything right now.

Finally, if you do create a new thread, kindly mark this one as solved.

[Piticu]

Ok,i will start a new thread when i will have to do the VBA code.thanks for help