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

 

[L. Howard]

We'll most likely muddle thru it, it is not only difficult to explain what you want, its difficult to look at a very busy worksheet for the first time and figure stuff out.
Some are more difficult than others.


For instance:

"...class and size on the darcy sheet. it will then put the diameter in cell E9"

Do you really mean exactly that cell E9?
And on the darcy sheet?

Are you against using input boxes for each of the criteria and allowing vba cope post the result in a distinct cell of your choice?

I have code that looks up four items and returns the fifth, I am in the process of converting it to look for three and return the fourth.

Howard

 

[WoodyZA]

https://dl.dropboxusercontent.com/u/84670331/excel explanation.png

here is a screen grab with some explantions does this help?

i don't mind using was ever works i would just like to take out the manual input of pipe diameter as it is now.

 

[L. Howard]

Yes, that confirms what I was beginning to grasp.

I'm on it.

Howard

 

[L. Howard]

Give this a test flight.

Look for button 170 on the Darcy-w sheet about cell G5.

Click it and you will get three Input Boxes asking for Material, then Nomn Dta., Class. Enter and click okay for each.
You will get a return of what you entered in the input boxea in cell F5 and the Inside Dia. in F6. ( Notice the two grey out cells)


ttps://www.dropbox.com/s/j65d74y98rgr6hy/Copy%20of%20pipe%20fiction%20test%20with%20Input%20box%20Drop%20Box.xlsm

Howard

 

[WoodyZA]

Thanks ill give it a whirl now and let you know in the morning
thanks you very much for the effort so far

 

[L. Howard]

Discard all previous versions of the sample workbooks. Open this and read the summary below about what you have.

https://www.dropbox.com/s/3pjmbptt7ih8b10/Pipe%20friction%20with%20two%20codes%20Drop%20Box%20Version.xlsm

Start with sheet Pipe Type.

See the red arrow pointing to grayed out cells O2 & P2.
Note buttons labeled Input Box & Drop Down.

Go to Darcy-Weisbach.

See the red arrow pointing to the grayed out cells F5 & F6.
Note buttons Input Box & Drop Down.
See blue shaded cells C5, D5 & E5. All have drop downs in them.

There is one code for each button.

The input values along with the "found" value will be posted on both sheets in the grayed out cells. This is true for either button.

The location of all of these things I just mentioned can be relocated or deleted depending on how you want you sheets.

Start with the Input Box button, click it and enter accurate data in each of the three input box prompts. When you hit OK on the third prompt, the code searches and return the inputs you made along with the "found" data into the grayed out cell on both sheets.

Do a few with the input button and carefully check the results for accuracy.

Now lets try the drop down code. Select proper data from each drop to display in the respective cell. When you have three criteria selected, click the Drop down button.
The results are display just like the Input Box data was on both sheets in the grayed out cells.

Again check carefully for accuracy.

Question, please ask.

Regards,
Howard

 

[WoodyZA]

Hello Howard thanks for all the help so far it is very much appreciated.

you have given me a far simpler way of getting to the same position i was before thanks. now would it be possible for the drop down boxes to be unique to i.e you you select Ductile Iron you can only select nominal diameters associated with the Ductile Iron (so you can choose 125mm etc). and based on the that selcted you only get to select from the available classes i.e for ductile irion you can only get class K7, K9 or C40?

the main problem i have is getting these selective dropdown menus to be be based on the selection in the previous menu

Thanks
Mat

 

[L. Howard]

I sensed that was looming over the hill once I got more familiar with the worksheet. Sorta back to aquare one.

Can you supply a list of:

Diameters available for each choice of Material,
Classes available for a Material and/or Diameter

Not sure I worded that correctly, but need to know when first item is selected, what are the second choice items that it dance with, and when we have two items what is the only choices that can dance with those two.

So, what we are saying is I can't select .025 inch wall thickness Cardboard Tube and then have a choice on 25,000 PSI going through it so to speak.

Howard

 

[L. Howard]

Never mind the list of stuff, I made one from the workbook.

A bit of a tough nut to crack, but I'll give it a go.

Howard

 

[WoodyZA]

I can make lists of the second and third selections. But i was trying to get the workbook to do it for me. If you have a look at my very "crude" attempt in the first sheet to get it do do the lists for me (the button i made), i only got as far as the second list the third becomes very complicated and that is where my problem lies.

yes you are correct in the selection example you gave.

once again thanks for the time and effort you have put in Howard.
Mat