Help Needed. 3 drop-down lists in a separate worsheet excel 2010

[WoodyZA]

Hello all
I am having trouble finding a way to do the following and any help would be very much appriciated:
I have a list of different types of pipes which will be updated from time to time in there seperate table. Example table below The actual List however is 1000 lines. (note the last two columns are calculation results based on othe column omitted for clarity, the rest are data entries)

Material	Pipe Nominal Diameter	Pipe pressure class	Actual Internal Diameter	Velocity of fluid
uPVC	200	Class 20	186	10
Ductile Iron	200	C30	201	15
Steel	150	4.5mm 300W	153	1
oPVC	63	Class 16	52	2
uPVC	160	Class 16	156	5
Ductile Iron	250	K9	251	6
HDPe	450	PN 16 PE 100	402	8
HDPe	50	PN 12.5 PE100	42	55
OPVC	75	Class 6	65	68

<tbody>
</tbody>

What i need is to be able to select the pipe material via a drop down list in a separate work sheet (but must only be unique entries) from this selection it will allow me to select the unique pipe diameters in the next cell over drop down list. then from these two it will allow me to select the unique pressure class from the third drop down list. from these three selections i will then populate cells 4 and 5 next to the drop down lists with the corresponding data in columns 4 and 5 in the above table.

I'm sorry if i have missed a similar example in the forum my seach returned alot of data validation examples but i have tried it using a little vb and the advanced filter function but all i can get to is the pipe diameter after that it is beyond my abilities i think.

Thank for the help
Mat

 

[WoodyZA]

Hello Howard
have you had any luck with this? i tried it now from a different angle and ended up in the same place . im starting to think that excel is not the program for this

 

[L. Howard]

Some luck along with perhaps another approach.

The first one is quite a monster to the non pro, I've gotten a ton of help to get it as far as it is, seems to still have a few warts. It may be very difficult to add and delete new and old data from the helper sheet, then have the code work properly afterward.

The second one is devised by the pro who has helped me a ton on the first one. Pretty much his take on the whole thing asking "why not do it this way?" This almost will be a snap to add or delete new and old data to the helper sheet.

Here is the first:
https://www.dropbox.com/s/g1b3a9re3di71a0/AA%20Copy%20of%20Drop%20Down%20Version%20two%20Drop%20Box.xlsm

Here is the second:
https://www.dropbox.com/s/c72brheeseimrq6/Drop Down Reversed Mat. Dia. Class Drop Box.xlsm

First one select a Material in any one of A1:A20 cells, in the same row in column B select a diameter, and in same row in the C column, select an inside diameter.
The return results have been replaced with the Material - Dia. - Class - ID. (made it easier to check for errors and there are some in the fix as we speak)

The other one has lists of the materials and you select one and it give you all the options for that selection. When the option is selected, code dose a text to columns on it into the next three columns.

See what you think, and of course once completed will need to be installed in your real workbook. Might be you will want to keep it as a separate sheet to do the searches.
You will probably have many question, so post back as needed.

Regards,
Howard

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[L. Howard]

This one seems to be a finished product. I re-instated the original return ID values, so when you select your Mat., Dia., & Class you will get a 'for real number' as a return.

I would delete the "First" version in the previous post. Keep the second Reversed version and play with both it and this finished product.

Howard

https://www.dropbox.com/s/pvu9bfagoc32nx6/Drop%20Down%20Mat.%20Dia.%20Class%20Best%20Version%20Drop%20Box.xlsm

 

[L. Howard]

And a revised version of the second REVERSED DROP DOWN

https://www.dropbox.com/s/819i4fszrj8l6aj/Drop%20Down%20Reversed%20Mat.%20Dia.%20Class%20Test%20Drop%20Box.xlsm


Regards,
Howard

 

[WoodyZA]

Hello Howard thanks for all the help to date. these two Spreadsheets seem to do what i need but i am a little lost on how the code works (so give me a day to two to look and teach myself whats happending) but in both cases it seems that when you add to the original lists it does not auto update the drop down lists. how can i add additional material etc. eg steel pipes.
Thanks again
Mat

 

[L. Howard]

From my post #22

It may be very difficult to add and delete new and old data from the helper sheet, then have the code work properly afterward.

I will inquire as to adding to and deleting new items on the first, original code.

The second version (the Reversed Version) I think you can just add or delete to the original list on the helper sheet. I believe at present the list is 100 rows. To add something like Steel, which is not already in the fray will require another column or two I believe. I'll look it over.

Howard

 

[L. Howard]

Here are two links to a way to make additions to each drop down workbooks.

On the first one, I added Steel and Copper. All additional code lines for Steel are marked off to the right with '// and Copper is marked with '/****.
Not a particularly easy task, depending on how well you can grasp the code and methodology.

https://www.dropbox.com/s/871ejmj4kv92n4r/AAA%20Add%20Steel%20Copper%20Mat.%20Dia.%20Class%20Drop%20Box.xlsm


On the second one I found it much easier to add Steel on the Helper Sheet. It really just the second drop down in a dependent drop down scenario. Code is pretty simple, just a text to columns thing. Note the code is looking for a delimiter of - (dash) so that is also noted in the visible comment on the Helper Sheet in the gray shaded area of the Steel addition.

https://www.dropbox.com/s/nimu8yn9n...ersed Add Steel Mat. Dia. Class Drop Box.xlsm

Regards,
Howard