Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated validation and pivot
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. -- Boris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Report choosing a Pivot table data | Excel Worksheet Functions | |||
Show do I share a dynamic list for data validation? | Excel Discussion (Misc queries) | |||
Data validation Formula | Excel Discussion (Misc queries) | |||
validation and omitting blank vaues from list | Excel Worksheet Functions | |||
Pivot table woes. | Excel Worksheet Functions |