B
BorisS
I need a complicated validation for some user entry.
I have the following setup:
I) list that shows a style number, a size code, and 12 columns, with a "G"
or no value in each.
II) a list of the size codes, with 12 columns, each of which has a size in
them. So for one size code, the values could be [blank, 2, 4, 6, blank x 8
columns], and for the next size code, the values could be [0, 2, 4, 6, 8, 10,
blank x 6 columns]. There are always 12 columns, and there are always either
blanks or some alpha-numeric value in the columns.
To use the two together, one must look at the size code next to the style in
the first list, and then based on the column in which a G appears, it is
possible to determine one of the several sizes which that style comes in
(there can be as few as 1 G and as many as 12, each in its own column. So
for two different entries, you could have a G in the same column, but because
the style codes are different, those Gs are indicating the availability of
two different sizes for the styles.
I've made a set of 12 columns to the right of the G columns, and done a
simple lookup of the size code, and what that size code means in terms of
value for the particular column that the G is in. In other words, if there
are 2 Gs on a style line, then by looking up the style key, I can determine
what the 2 Gs represent in terms of sizes.
My challenge now is to have a validation which uses this data. I need to
have a user select a style from a dropdown (simple enough), and then based on
that selection, show the sizes which the style comes in. I cannot make named
ranges for each of the styles, as there are an inordinate number of them.
I have thought of something like the following, but am stuck. I want to do
a pivot of the styles listed down vertically, with the sizes showing up next.
I am having trouble with this step. I cannot seem to convert the
horizontally laid out size columns that appear on each style's line, into a
vertical listing that appears right after the style field in the pivot. To
illustrate, I'll have the style line look like this (assume there are 12 size
columns, but simplified here for demo purpose)
A B C D E
F
Style Size1 Size2 Size3 Size4
Size5
1234PV [blank] 2 4 6
[blank]
1234HD 16WP 18WP 20WP 22WP [blank]
and each line of the hundreds that I'll have, will have the style number and
these size columns, with no rhyme or reason to the values that appear in the
size columns.
So what I'd like to see as a first step is that list in a pivot that shows
this:
Style Size Count
1234PV 2
4
6
1234HD 16WP
18WP
etc.
I put in the count data value, only because the values of sizes cannot serve
as data, since they will show up as counts, rather than the actual
alpha-numeric values. For the data field, I'll throw something in there to
get it to work. But I don't know how to structure the size columns to show
up vertically.
Further, once I have that listing, I was hoping I'd be able to come up with
some formula that basically does the following logic:
Lookup the style number that's selected in the previous dropdown (as
reminder, the user selects a style, and then sees the next cell's validation
as being the list of sizes available), then show the possibilities that are
not blanks for the 12 size fields.
That's basically the challenge. I actually don't think it's that hard, and
probably just longer to explain than to do. But I'm getting stumped quite
badly on this one.
Thx for any help.
I have the following setup:
I) list that shows a style number, a size code, and 12 columns, with a "G"
or no value in each.
II) a list of the size codes, with 12 columns, each of which has a size in
them. So for one size code, the values could be [blank, 2, 4, 6, blank x 8
columns], and for the next size code, the values could be [0, 2, 4, 6, 8, 10,
blank x 6 columns]. There are always 12 columns, and there are always either
blanks or some alpha-numeric value in the columns.
To use the two together, one must look at the size code next to the style in
the first list, and then based on the column in which a G appears, it is
possible to determine one of the several sizes which that style comes in
(there can be as few as 1 G and as many as 12, each in its own column. So
for two different entries, you could have a G in the same column, but because
the style codes are different, those Gs are indicating the availability of
two different sizes for the styles.
I've made a set of 12 columns to the right of the G columns, and done a
simple lookup of the size code, and what that size code means in terms of
value for the particular column that the G is in. In other words, if there
are 2 Gs on a style line, then by looking up the style key, I can determine
what the 2 Gs represent in terms of sizes.
My challenge now is to have a validation which uses this data. I need to
have a user select a style from a dropdown (simple enough), and then based on
that selection, show the sizes which the style comes in. I cannot make named
ranges for each of the styles, as there are an inordinate number of them.
I have thought of something like the following, but am stuck. I want to do
a pivot of the styles listed down vertically, with the sizes showing up next.
I am having trouble with this step. I cannot seem to convert the
horizontally laid out size columns that appear on each style's line, into a
vertical listing that appears right after the style field in the pivot. To
illustrate, I'll have the style line look like this (assume there are 12 size
columns, but simplified here for demo purpose)
A B C D E
F
Style Size1 Size2 Size3 Size4
Size5
1234PV [blank] 2 4 6
[blank]
1234HD 16WP 18WP 20WP 22WP [blank]
and each line of the hundreds that I'll have, will have the style number and
these size columns, with no rhyme or reason to the values that appear in the
size columns.
So what I'd like to see as a first step is that list in a pivot that shows
this:
Style Size Count
1234PV 2
4
6
1234HD 16WP
18WP
etc.
I put in the count data value, only because the values of sizes cannot serve
as data, since they will show up as counts, rather than the actual
alpha-numeric values. For the data field, I'll throw something in there to
get it to work. But I don't know how to structure the size columns to show
up vertically.
Further, once I have that listing, I was hoping I'd be able to come up with
some formula that basically does the following logic:
Lookup the style number that's selected in the previous dropdown (as
reminder, the user selects a style, and then sees the next cell's validation
as being the list of sizes available), then show the possibilities that are
not blanks for the 12 size fields.
That's basically the challenge. I actually don't think it's that hard, and
probably just longer to explain than to do. But I'm getting stumped quite
badly on this one.
Thx for any help.