Drop Downs, Index Match or what?
 

Hi Howard,

Am Thu, 17 Oct 2013 17:22:04 -0700 (PDT) schrieb :

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.

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

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.

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.

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

Drop Downs, Index Match or what?
 

Hi Howard,

Am Fri, 18 Oct 2013 10:09:10 -0700 (PDT) schrieb :

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

All not needed strings in declaration part can be deleted.

Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

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.

Here is a link, if you care to take a look.
https://www.dropbox.com/s/92isw0vozs...rop%20Box.xlsm


Thanks.
Howard

Drop Downs, Index Match or what?
 

Typo

End Sub is highlighted with the error message.

H'wd

Drop Downs, Index Match or what?
 

Hi Howard,

Am Fri, 18 Oct 2013 23:12:26 -0700 (PDT) schrieb :

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.

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=9378A...121822A3%21326
for the workbook "DropDown"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

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=9378A...121822A3%21326

for the workbook "DropDown"

Regards

Claus B.



Great, thanks Claus.

A little clean up and I hope its done.

Howard

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sat, 19 Oct 2013 01:56:56 -0700 (PDT) schrieb :

A little clean up and I hope its done.

all strings that are equal are only one time needed


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

all strings that are equal are only one time needed

Sorry, I don't understand what you mean.

Howard

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sat, 19 Oct 2013 06:57:56 -0700 (PDT) schrieb :

Sorry, I don't understand what you mean.

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


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sat, 19 Oct 2013 06:57:56 -0700 (PDT) schrieb :

Sorry, I don't understand what you mean.

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


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

On Saturday, October 19, 2013 7:36:35 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sat, 19 Oct 2013 06:57:56 -0700 (PDT) schrieb :



Sorry, I don't understand what you mean.



please have another look for "Drop Down"

I hope I have all duplicates deleted





Regards

Claus B.


Okay, got it.

Looks like all I have to do is chase down some "funny" returns and/or a few "no returns"

Sure appreciate your help.

Howard

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sat, 19 Oct 2013 08:22:20 -0700 (PDT) schrieb :

Looks like all I have to do is chase down some "funny" returns and/or a few "no returns"

I made some changes in the code
But I am not sure with mPVC and uPVC
Have both material the diameter 315, but uPVC has 335 and mPVC has 355?
So it is in the workbook on Skydrive
Some values are missing in the helper sheet. So the formula returns #N/A


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

On Saturday, October 19, 2013 8:41:35 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sat, 19 Oct 2013 08:22:20 -0700 (PDT) schrieb :



Looks like all I have to do is chase down some "funny" returns and/or a few "no returns"



I made some changes in the code

But I am not sure with mPVC and uPVC

Have both material the diameter 315, but uPVC has 335 and mPVC has 355?

So it is in the workbook on Skydrive

Some values are missing in the helper sheet. So the formula returns #N/A





Regards

Claus B.


Now that I have the concept down for the most part, I'm building a workbook one Material at a time, starting with uPVC. I just entered a1 to axxx for values, darned if all is well until I get to uPVC - 335 - Class 4 thru 16.. Should return a86-90 but I get #N/A for all 335. Index formula covers down to row 112 (for now) and 335 is row 87. I've been staring at it for 20 minutes. All numbers of uPVC before 335 are accurately returned.

Hopefully it will dawn on me what's happening.

Howard

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sat, 19 Oct 2013 12:56:39 -0700 (PDT) schrieb :

Now that I have the concept down for the most part, I'm building a workbook one Material at a time, starting with uPVC. I just entered a1 to axxx for values, darned if all is well until I get to uPVC - 335 - Class 4 thru 16. Should return a86-90 but I get #N/A for all 335. Index formula covers down to row 112 (for now) and 335 is row 87. I've been staring at it for 20 minutes. All numbers of uPVC before 335 are accurately returned.

that is what I wrote. Did you see that in the helper sheet is a diameter
of 355 instead 335?


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

Hi again,

Am Sat, 19 Oct 2013 22:03:30 +0200 schrieb Claus Busch:

that is what I wrote. Did you see that in the helper sheet is a diameter
of 355 instead 335?

what is correct? The 335 in the DV or the 355 in the helper sheet.
Same thing with mPVC


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

On Saturday, October 19, 2013 1:04:43 PM UTC-7, Claus Busch wrote:
Hi again,



Am Sat, 19 Oct 2013 22:03:30 +0200 schrieb Claus Busch:



that is what I wrote. Did you see that in the helper sheet is a diameter

of 355 instead 335?



what is correct? The 335 in the DV or the 355 in the helper sheet.

Same thing with mPVC





Regards

Claus B.


I see. 355 is the correct number.

Howard

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sat, 19 Oct 2013 13:45:48 -0700 (PDT) schrieb :

I see. 355 is the correct number.

I have corrected it in Skydrive.
The string as well as the Select case statement. The formula is now
working


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

I have corrected it in Skydrive.

The string as well as the Select case statement. The formula is now

working


Regards

Claus B.


I believe this is the version you updated. I have found some error I don't how to fix.

First I replaced all the return value to values like this:

uPVC-50-Class 4

Which will match what is in the drop downs,[Material - Dia - Class] if the return is correct.

You can see in A2 mPVC but the return is uPVC.

Howard

Drop Downs, Index Match or what?
 

On Saturday, October 19, 2013 6:29:05 PM UTC-7, wrote:


I have corrected it in Skydrive.



The string as well as the Select case statement. The formula is now



working





Regards



Claus B.





I believe this is the version you updated. I have found some error I don't how to fix.



First I replaced all the return value to values like this:



uPVC-50-Class 4



Which will match what is in the drop downs,[Material - Dia - Class] if the return is correct.



You can see in A2 mPVC but the return is uPVC.



Howard

Forgot to post the link.

https://www.dropbox.com/s/c8g9s2qmgj...p%20Box.x lsm

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sat, 19 Oct 2013 19:53:51 -0700 (PDT) schrieb :

https://www.dropbox.com/s/c8g9s2qmgj...p%20Box.x lsm

so uPCV and mPVC have the same diameters and classes it is not enough to
match columns B & C. I now match A & B & C and the returns are correct.
But I ran in another error because in the CV you have "Duct Iron" and in
the helper sheet it is "Ductile Iron". So I get #N/A and I changed the
Ductile Iron to Duct Iron to fix it.
Look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for "Drop Down Version two"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sat, 19 Oct 2013 19:53:51 -0700 (PDT) schrieb :

https://www.dropbox.com/s/c8g9s2qmgj...p%20Box.x lsm

in D you have a string that concatenat A&B&C.
Why do you solve this with INDEX/MATCH?
You could do this with (in D1 and copy down):
=IF(COUNTA(A1:C1)<3,"",SUBSTITUTE(A1&" -"&B1&" -"&C1,"Duct","Ductile"))


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

Look he

https://skydrive.live.com/#cid=9378A...121822A3%21326

for "Drop Down Version two"

Regards

Claus B.

Excellent. There are so many things that sneak up on a person in building a workbook like this.

No way it gets done without your help.

Thank you.

Regards,
Howard

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sun, 20 Oct 2013 01:50:02 -0700 (PDT) schrieb :

Excellent. There are so many things that sneak up on a person in building a workbook like this.

thank you for the feedback. I am glad that it is working now.

What is the reason to do it this way? It is much easier to create a DV
with the products and split it to material, diameter and class.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

What is the reason to do it this way? It is much easier to create a DV

with the products and split it to material, diameter and class.


Regards

Claus B.


Well, mostly I was responding to a post and it seemed easily enough solved by a couple of drop downs, the second drop down choices dependent on what was chosen in the first.

Then it was, okay now that I have those two choices, the third drop down must depend on those choices.

I found examples of ways to do that but also found it very difficult to follow the site instructions on how to make that work.

So when I posted here looking for some perhaps simpler method, I just went along with what was offered. When I got an example work book I knew I was in over my head from the vast amounts of combinations that I was dealing with.

I try to stay within my skill level in my responses, mostly only needing an occasional kick in the pants here or there to get me by. But every once in a while they evolve to what seems to be a monster to me. This one was way more than I could handle as you can see by all the help I needed.

I would be interested in the method you mention of creating a DV w/ the products and split it to mat., dia., and class.

I fear the one you just finished for me will be almost impossible for the end use to maintain if materials and diameters change very much.

Howard

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sun, 20 Oct 2013 05:09:12 -0700 (PDT) schrieb :

I would be interested in the method you mention of creating a DV w/ the products and split it to mat., dia., and class.

look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for "DropDownReversed"

In column A you do a check for the material. Then you get in column B
all the product with this material and choosing a product will be
splitted to material, diameter and class.
In HelperSheet you can insert new products or delete a product. The name
goes to row 100.
Depending DVs cannot handle dynamic names


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sun, 20 Oct 2013 15:28:33 +0200 schrieb Claus Busch:

https://skydrive.live.com/#cid=9378A...121822A3%21326
for "DropDownReversed"

you could change the layout of the helper sheet that there are all
available diameters and the depending materials.
Into the main sheet you could choose the diameter, see all available
material for this diameter and if you choose a material formula can
return product an class.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sun, 20 Oct 2013 17:06:44 +0200 schrieb Claus Busch:

https://skydrive.live.com/#cid=9378A...121822A3%21326
for "DropDownReversed"

please have another look:
https://skydrive.live.com/#cid=9378A...121822A3%21326
for "Drop Down Test"

I guess it is easier in handling


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

On Sunday, October 20, 2013 9:33:50 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 20 Oct 2013 17:06:44 +0200 schrieb Claus Busch:



https://skydrive.live.com/#cid=9378A...121822A3%21326

for "DropDownReversed"



please have another look:

https://skydrive.live.com/#cid=9378A...121822A3%21326

for "Drop Down Test"



I guess it is easier in handling





Regards

Claus B.


That looks very promising.

I'm still finding ghosts in the other method, as I check ALL possible returns for errors. DuctIron with a 400 or 450 dia errors out. I find it hopeless to try to track down what's happening.

This new method looks like it would be much easier to maintain, add or delete new and old data to the Helper Sheet.

I'll give it a good look over.

Thanks Claus.

Howard

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sun, 20 Oct 2013 12:30:16 -0700 (PDT) schrieb :

I'm still finding ghosts in the other method, as I check ALL possible returns for errors. DuctIron with a 400 or 450 dia errors out. I find it hopeless to try to track down what's happening.

I didn't found the error. So I changed the code to a version with more
overview. It seems to work. Please test it:
"Drop Down Version two"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

On Sunday, October 20, 2013 1:12:05 PM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 20 Oct 2013 12:30:16 -0700 (PDT) schrieb :

I didn't found the error. So I changed the code to a version with more

overview. It seems to work. Please test it:

"Drop Down Version two"

Regards

Claus B.

I tested every return and was able to repair a DuctIron 300 which did not exist and DuctIron 350 K9 returns #N/A. This might have been due to a typo on Helper Sheet with 350 K8 instead of K9. I changed everything I could find to K9 (pertaining to DuctIron 350) but no success.

GRP SN 10000 STIS refuses to work for me. Errors out on ant Dia. selected.

https://www.dropbox.com/s/g1b3a9re3d... p%20Box.xlsm

Howard

Drop Downs, Index Match or what?
 

Am Sun, 20 Oct 2013 19:32:21 -0700 (PDT) schrieb :

On Sunday, October 20, 2013 1:12:05 PM UTC-7, Claus Busch wrote:
Hi Howard,

Am Sun, 20 Oct 2013 12:30:16 -0700 (PDT) schrieb :

I didn't found the error. So I changed the code to a version with more

overview. It seems to work. Please test it:

"Drop Down Version two"

Regards

Claus B.

I tested every return and was able to repair a DuctIron 300 which did not exist and DuctIron 350 K9 returns #N/A. This might have been due to a typo on Helper Sheet with 350 K8 instead of K9. I changed everything I could find to K9 (pertaining to DuctIron 350) but no success.

GRP SN 10000 STIS refuses to work for me. Errors out on ant Dia. selected.

https://www.dropbox.com/s/g1b3a9re3d... p%20Box.xlsm

Howard


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

Hi Howard,

Am Sun, 20 Oct 2013 19:32:21 -0700 (PDT) schrieb :

GRP SN 10000 STIS refuses to work for me. Errors out on ant Dia. selected.

please look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for "Drop Down Version two"
There was a typo in the code
Instead of GRP there was GPR


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

please look he

https://skydrive.live.com/#cid=9378A...121822A3%21326

for "Drop Down Version two"

There was a typo in the code

Instead of GRP there was GPR


Regards

Claus B.


Well, I finished testing each possible return and all seem great!

I really appreciate ALL the help.

I like the reversed model you offered also. That may be a simpler solution or maybe use the two in combo, (meaning both available to consult, NOT as a combined unit.)

Regards,
Howard

Drop Downs, Index Match or what?
 

Hi Howard,

Am Mon, 21 Oct 2013 02:32:18 -0700 (PDT) schrieb :

Well, I finished testing each possible return and all seem great!

glad to help.

I like the reversed model you offered also. That may be a simpler solution or maybe use the two in combo, (meaning both available to consult, NOT as a combined unit.)

Did you also look for "Drop Down Test"?


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

On Monday, October 21, 2013 3:04:12 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Mon, 21 Oct 2013 02:32:18 -0700 (PDT) schrieb :



Well, I finished testing each possible return and all seem great!



glad to help.



I like the reversed model you offered also. That may be a simpler solution or maybe use the two in combo, (meaning both available to consult, NOT as a combined unit.)



Did you also look for "Drop Down Test"?





Regards

Claus B.


Just now did, and that really looks like it should be of use.

Pretty clever. If I'm seeing it correctly you can start out with any one of the three items and then choose the other two from the choices it offers.

This really does have possibilities!

Thank, Claus.

Howard

Drop Downs, Index Match or what?
 

Well, I finished testing each possible return and all seem great!

Hi Claus,

I am attempting to add an additional Material to the sheet and code.

So far I've added "Steel" to the drop down list.
I have added Steel, Dia., Class and ID to the Helper Sheet (all phony data for testing)
I have extended the Index formula range to row 530.

Every line in the code that I have added to try to make "Steel" work, is followed by '// so you can see where I have made the additions.

The code errors out he

With Target.Offset(, 1).Validation...

with Formula1:=myStr

myStr = ""

Howard

Link to my test sheet:

https://www.dropbox.com/s/v0ffzlwrws...rop%20Box.xlsm

Drop Downs, Index Match or what?
 

Hi Howard,

Am Tue, 22 Oct 2013 09:58:26 -0700 (PDT) schrieb :

So far I've added "Steel" to the drop down list.
I have added Steel, Dia., Class and ID to the Helper Sheet (all phony data for testing)
I have extended the Index formula range to row 530.

look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for workbook "AAA Data Drop Down"

It seems to work.

Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Drop Downs, Index Match or what?
 

It seems to work.



Regards

Claus B.


Yes, it does. That should help immensely.

Thanks,
Howard

Drop Downs, Index Match or what?
 

Hi Howard,

Am Tue, 22 Oct 2013 10:48:05 -0700 (PDT) schrieb :

Yes, it does. That should help immensely.

if you have strings like St 1, St2 then these strings have clear
indices. So you can put them in an array and call it by index.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2