Drop Downs, Index Match or what?

[lhkittle]

Hi Howard,



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






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

 

[Claus Busch]

Hi Howard,

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

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.

 

[lhkittle]

Hi Howard,



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






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 workbookone Material at a time, starting with uPVC. I just entered a1 to axxx forvalues, 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

 

[Claus Busch]

Hi Howard,

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

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.

 

[Claus Busch]

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.

 

[lhkittle]

Hi again,



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







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

 

[Claus Busch]

Hi Howard,

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

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.

 

[lhkittle]

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

 

[lhkittle]

I have corrected it in Skydrive.

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






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/c8g9s2qmgjwt7ef/Drop Down Version one Claus Drop Box.xlsm

 

[Claus Busch]

Hi Howard,

Am Sat, 19 Oct 2013 19:53:51 -0700 (PDT) schrieb (e-mail address removed):

https://www.dropbox.com/s/c8g9s2qmgjwt7ef/Drop Down Version one Claus Drop Box.xlsm

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 here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Drop Down Version two"


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Sat, 19 Oct 2013 19:53:51 -0700 (PDT) schrieb (e-mail address removed):

https://www.dropbox.com/s/c8g9s2qmgjwt7ef/Drop Down Version one Claus Drop Box.xlsm

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.

 

[lhkittle]

Look here:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

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

 

[Claus Busch]

Hi Howard,

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

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.

 

[lhkittle]

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 anoccasional 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

 

[Claus Busch]

Hi Howard,

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

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 here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
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.

 

[Claus Busch]

Hi Howard,

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

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
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.

 

[Claus Busch]

Hi Howard,

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

please have another look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Drop Down Test"

I guess it is easier in handling


Regards
Claus B.

 

[lhkittle]

Hi Howard,



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






please have another look:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

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

 

[Claus Busch]

Hi Howard,

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

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.

 

[lhkittle]

Hi Howard,



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

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/g1b3a9re3di71a0/AA Copy of Drop Down Version two Drop Box.xlsm

Howard