Drop Downs, Index Match or what?

L

lhkittle

(Hope chart data post okay.)

I would like to have a method that when a Mat. is selected in A1, the choices for selections in B1 are limited to a certain amounts of Dia.

So if I select uPVC, then only 50 and 60 are available for a choice in B1.
Then if I select 50 for B1, then only Class 4, Class 6, or Class 9 is available for C1.

Once that hurdle is cleared, with uPvc - 50 - Class 9 selected in A1, B1, C1 a method to return 45.6 in D1.

mPVC - 63 - Class 6 would be invalid, since 63 is not a choice for any mPVC.

There are about six Materials, numerous Diameters and a large variance of Wall mm. and ID mm in a data set of about 500+ rows and four columns.

I have tried for hours to make dependent drop down's work to no avail.

Any suggestions on a sane method to get this done?

Thanks.
Howard

eMat. Dia. Wall mm ID mm
uPVC 50 Class 4 47
uPVC 50 Class 6 46.4
uPVC 50 Class 9 45.6

uPVC 63 Class 4 60
uPVC 63 Class 12 59.2
uPVC 63 Class 16 57.6

mPVC 75 Class 6 72
mPVC 75 Class 9 71.2
mPVC 75 Class 12 70

mPVC 90 Class 6 86.4
mPVC 90 Class 20 80.4
mPVC 90 Class 25 78.2

D Iron 100 C40 103.2
D Iron 100 K7 100
D Iron 100 K9 98
 
C

Claus Busch

Hi Howard,

Am Wed, 16 Oct 2013 17:42:13 -0700 (PDT) schrieb (e-mail address removed):
I would like to have a method that when a Mat. is selected in A1, the choices for selections in B1 are limited to a certain amounts of Dia.

So if I select uPVC, then only 50 and 60 are available for a choice in B1.
Then if I select 50 for B1, then only Class 4, Class 6, or Class 9 is available for C1.
Click to expand...

have a look:
http://www.contextures.com/xlDataVal13.html
http://www.contextures.com/xlDataVal02.html
http://www.contextures.com/xlDataVal15.html


Regards
Claus B.
 
L

lhkittle

Hi Howard,



Am Wed, 16 Oct 2013 17:42:13 -0700 (PDT) schrieb (e-mail address removed):







have a look:

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

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

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





Regards

Claus B.
Click to expand...


Those are the sites I have spent hours trying to make it work for my situation, one example takes the drop downs to "any" number of columns.

I'll have to dive back into that one and see if I can wring something out.

Was hoping for a different "concept", if one exists, to try.

Don't want someone to write it for me, just looking for perhaps another way to approach it.

Thanks. I'll plug away.

Regards,
Howard
 
L

lhkittle

Hi Howard,



Am Thu, 17 Oct 2013 14:39:07 +0200 schrieb Claus Busch:







on a new sheet there is also a suggestion with names and depending data

validation. Download the workbook because VBA is disabled in SkyDrive





Regards

Claus B.
Click to expand...

Very promising, I thank you.

A little study time coming up but I'm thing I get the drift of it.

Thanks.

Regards,
Howard
 
C

Claus Busch

Hi Howard,

Am Thu, 17 Oct 2013 09:31:49 -0700 (PDT) schrieb (e-mail address removed):
A little study time coming up but I'm thing I get the drift of it.
Click to expand...

I think that you will not have problems with the understanding of the
code.
For the depending DVs you can look in Name Manager and set the cursor in
the reference bar and the range of the name will get running ants. How
to use the names you can see in the DV formula.


Regards
Claus B.
 
L

lhkittle

Hi Howard,



Am Thu, 17 Oct 2013 09:31:49 -0700 (PDT) schrieb (e-mail address removed):






I think that you will not have problems with the understanding of the

code.

For the depending DVs you can look in Name Manager and set the cursor in

the reference bar and the range of the name will get running ants. How

to use the names you can see in the DV formula.





Regards

Claus B.
Click to expand...


Hi Claus,

Would you mind having a look at what I've got so far. Once I started fleshing this out it seems to have grown into a small monster.

Hope I am going in the right direction.

Still a work in progress to be sure. (I get a variable not declared error from doing stuff on one of the sheets once in a while...? when I see what ishighlighted it looks like part of the original code I will delete later, but have left it in for a reference of what should be done with newer code and the much expanded scope of the code)

I think I am ready to adjust the Select Case portion of the code and not sure how to do so with all the new stuff now in play.

So, two things, one, am I heading in the correct direction with all the Dim's and such, two could you jump start me on the Select Case portion as how it would pertain to the new code?

The drop down usage on the sheets has me puzzled, but if the code functionsproperly (as yours did with the small sample of data used) then I can probably figure out how to use them properly.

https://www.dropbox.com/s/bdku6hyt1tm0ni9/Claus Drop Down Val setup Tester Drop Box.xlsm

Thanks.
Howard
 
C

Claus Busch

Hi Howard,

Am Thu, 17 Oct 2013 17:22:04 -0700 (PDT) schrieb (e-mail address removed):
Would you mind having a look at what I've got so far. Once I started fleshing this out it seems to have grown into a small monster.
Click to expand...

it is not a real monster. Most of the strings you don't need. Look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for workbook "Drop Down Val"
All the out commented lines you don't need and so you can summarize them
into the Select Case statement.
I have done it up to the diameter of 250.

But how will you Excel or VBA tell what is str25GRPSN300 or
str50GRPSN300. I guess you have to change the values for that material
into the data table.


Regards
Claus B.
 
C

Claus Busch

Hi Howard,

Am Thu, 17 Oct 2013 17:22:04 -0700 (PDT) schrieb (e-mail address removed):
Would you mind having a look at what I've got so far. Once I started fleshing this out it seems to have grown into a small monster.
Click to expand...

the materials and diameters are adapted except all GRPSN materials
because I don't get them sorted. You have to add another character or
number to the data table that the different values can be recognized.


Regards
Claus B.
 
L

lhkittle

the materials and diameters are adapted except all GRPSN materials

because I don't get them sorted. You have to add another character or

number to the data table that the different values can be recognized.
Click to expand...
Regards

Claus B.
Click to expand...

Looking very nice. I'll get after finishing it now that I have some solid and proper code to look as example.
But how will you Excel or VBA tell what is str25GRPSN300 or
str50GRPSN300. I guess you have to change the values for that material
into the data table.
Click to expand...

So I need to go to the worksheet list and do something like:

For the 300 Dia.
GRP SN 2500 STIS-30
GRP SN 2500 STIS-30

For the 350 Dia.
GRP SN 2500 STIS-35
GRP SN 2500 STIS-35

And so on.

Thanks for the additional direction.

Howard
 
C

Claus Busch

Hi Howard,

Am Fri, 18 Oct 2013 10:09:10 -0700 (PDT) schrieb (e-mail address removed):
For the 300 Dia.
GRP SN 2500 STIS-30
GRP SN 2500 STIS-30
Click to expand...

you have e.g.
str25GRPSN350
str50GRPSN350
str10GRPSN350

so you have to add 25, 50 and 10 to the diamters 300, 350, 400, 450 and
500
All not needed strings in declaration part can be deleted.


Regards
Claus B.
 
L

lhkittle

All not needed strings in declaration part can be deleted.
Click to expand...
Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Hi Claus,

I have made some progress, got all materials to work to a degree, including the pesky

GRP SN 2500 STIS
GRP SN 5000 STIS
GRP SN 10000 STIS

I was checking accuracy of the returns starting with cell A1 and selected uPVC
and 50 in the drop down in B1 and C1 only displays Class 6 and Class 9. It should offer

Class 4
Class 6
Class 9
Class 12
Class 16

for uPVC and 50.

I tried other drop col A downs for no apparent reason to see if there was any differences and bing out of the blue I start getting an error message in the
Select Case - No select case with end case or such and the End Sum is blue highlighted.

I parsed it to the best of my knowledge and cannot find a reason for the error message or why all the classes are not displayed in the C1 drop down.

I am un sure which strings can be deleted as you mention here.
All not needed strings in declaration part can be deleted.
Click to expand...

Here is a link, if you care to take a look.
https://www.dropbox.com/s/92isw0vozsw6yy0/Drop Down MSClaus Drop Box.xlsm


Thanks.
Howard
 
C

Claus Busch

Hi Howard,

Am Fri, 18 Oct 2013 23:12:26 -0700 (PDT) schrieb (e-mail address removed):
I was checking accuracy of the returns starting with cell A1 and selected uPVC
and 50 in the drop down in B1 and C1 only displays Class 6 and Class 9. It should offer

Class 4
Class 6
Class 9
Class 12
Class 16

for uPVC and 50.

I tried other drop col A downs for no apparent reason to see if there was any differences and bing out of the blue I start getting an error message in the
Select Case - No select case with end case or such and the End Sum is blue highlighted.
Click to expand...

you deleted a END SELECT and didn't initialize GRP SN 10000 STIS.
So you ran into an error and the code stopped working. That is why the
returned value was wrong.
For me it is working
Have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "DropDown"


Regards
Claus B.
 
C

Claus Busch

Hi Howard,

Am Sat, 19 Oct 2013 01:56:56 -0700 (PDT) schrieb (e-mail address removed):
A little clean up and I hope its done.
Click to expand...

all strings that are equal are only one time needed


Regards
Claus B.
 
C

Claus Busch

Hi Howard,

Am Sat, 19 Oct 2013 06:57:56 -0700 (PDT) schrieb (e-mail address removed):
Sorry, I don't understand what you mean.
Click to expand...

if e.g. strDuctIron80 =StrDuctIron450 = strDuctIron 600 etc.
you can delete the duplicates


Regards
Claus B.
 
C

Claus Busch

Hi Howard,

Am Sat, 19 Oct 2013 06:57:56 -0700 (PDT) schrieb (e-mail address removed):
Sorry, I don't understand what you mean.
Click to expand...

please have another look for "Drop Down"
I hope I have all duplicates deleted


Regards
Claus B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Difficult formula! Need help quick! 4
Look Up Values Within a Range 1
ganaral stors 0
LOOKUP function 3
Open a GroupWise .NAB File 6
interpolation with forecast in a particular interval (HELP) 4
OR clause doesn't execute the last condition 2
who can help please with table query ? 1

Top