Spare parts drop down list problem

**lewis63** · 09-08-2018, 06:48 AM

Hi
New to this forum & Excel.
i am currently trying to make a customer spare parts quotation but have an issue with selecting "customer" "machine" "serial No." drop down lists.

LIST.jpg

My problem is:- we have 4 main "types" of machine, some customers have only 1 machine of 1 type, some have many machines of one type and some up to 3 different types. so how do i get dependent drop down list's to show the choice of up to 4 machine types (if the customer has 3 then show the 3 he has) then once the "type" has been selected. show the serial No.s of all that machine type the customer has.
I have done this with data validation & named cells but as you can't have spaces in the "name" as with the customer name CUSTOMER.NAME or MACHINE25 this doesn't look good as an end result. Maybe someone knows if its possible to remove the "." and the "25" I have been colouring the text blue for now but its tedious

Would appreciate any help with this as i have been trawling through hundreds of drop down tutorials but none of which suit my dilemma

**mehmetcik** · 09-08-2018, 06:51 AM

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**lewis63** · 09-08-2018, 07:53 AM

Hi
Thanks for the quick response, here is a simplified version of what i have at the moment.
Not sure if its the correct way or not but need to get rid of the "." and "numbers" i had to use in naming tables.

**avk** · 09-08-2018, 08:02 AM

Your screen shot photo & attach file is different. Attach your actual file.

**lewis63** · 09-08-2018, 08:11 AM

hi AVK
Mehmetcik asked me to attach a sample, sorry i misread thought it was simple! Am I ok to leave the customer names and machine makes on it or do i need to replace
with made up info as there are quite a lot, 70ish? thanks..

**lewis63** · 09-08-2018, 08:14 AM

The attached "SIMPLE" is a very small version but exactly the same way i did the drop down lists in my real worksheet...

**lewis63** · 09-08-2018, 10:05 AM

Hi AVK
So,OK..
here is my desensitized worksheet, have included the "spares list" in case there is an easy way to enter the
part number on the the "quote" sheet and bring all the info from the "spares list " sheet into the "quote" sheet.
At the moment i am copy/pasting, any advise will be most welcome. Thanks..

**JeteMc** · 09-14-2018, 04:42 PM

As to the file attached to post #3:
1) The periods are replaced with spaces for customer names and machines,
2) The data validation for cell C4 has a source of: =INDIRECT(SUBSTITUTE(B4," ","."))
3) The data validation for cell D4 has a source of: =INDIRECT(SUBSTITUTE(C4," ","."))
Let us know if you have any questions.

**lewis63** · 09-14-2018, 06:09 PM

JeteMc
Thanks for the reply.
I think i may have confused the issue by suffixing my 4 types of machine a,b,c,d to match the customer, in reality there are only 4 "names of machine" so how can i differentiate the serial numbers if all 4 customers had the same type of machine? that's why in post #7 i had to suffix the "name box" with a number and its this I need to remove from the "quote" sheet. Please ask if you need any more info, thank you for your patience.

**JeteMc** · 09-14-2018, 09:13 PM

For the file attached to post #7 the steps are similar to my earlier post with respect to the names and drop down in C17.
As for the serial numbers:
1) You could remove the number suffixes from the machines,
2) Use the following as the source for the data validation in D17: =INDIRECT($C17&RIGHT(A17,LEN(A17)-SEARCH(" ",A17)))
Let us know if you have any questions.

**lewis63** · 09-17-2018, 05:38 AM

Hi JeteMc
I have tried your suggestion but does not seem to work maybe i have missed something.
have attached another version
the customer name is good all that's not is the machine suffix, not sure if when you remove the number from
the end if the name length has an effect on the formula so have shown first letter then _ to give you the
length. thanks again for your efforts...

**JeteMc** · 09-17-2018, 08:34 AM

Works well on my end with the exception of Customer 3 co
Is there a reason that the "co" needs to be added to that name?

**lewis63** · 09-17-2018, 09:35 AM

Our customer names vary, one/two/three or four words. e.g. JOE BLOGGS FRUIT CO / EVERGREEN FOODS / YUM YUM SNACKS, etc.

**JeteMc** · 09-17-2018, 10:41 AM

In that case it would seem that something similar to the attached would work better.
1) The actual customer name is in the drop down,
2) B4 (which may be hidden and/or moved for aesthetic purposes) references the name using:

Formula:

Please Login or Register  to view this content.

3) The data validation for machine reverts to simply: =INDIRECT(B4)
4) The data validation for serial number becomes: =INDIRECT($C4&RIGHT(B4,LEN(B4)-SEARCH(".",B4)))
Let us know if you have any questions.

**lewis63** · 09-17-2018, 10:55 AM

BOOM! Thats it!! now all i have to do is transfere the formula to the real spreadsheet... thanks again for your expertise

**JeteMc** · 09-17-2018, 02:57 PM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.

Spare parts drop down list problem

LinkBack

Thread Tools

Rate This Thread

Display

Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Re: Spare parts drop down list problem

Thread Information

Users Browsing this Thread

Similar Threads

Spare Parts Management Using Macros & VBA

Spare Parts Management

Choose an item from drop down list--> populate mulitple parts and their prices below

Scan parts OUT to Sheet1, parts IN to Sheet2 and list in Sheet3

ABC spare part list

problem with Drop List

Excel Catalogue item list parts Quantity problem

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions