VLOOKUP or INDIRECT(SUBSTITUTE) question

[chiguy41]

i have data in 3 areas
1. Store_Category. This has 4 items in it.
2. Style. This is the style of the 4 items in Store_Category, each item has multiple choices.
3. Sleeve. This is sleeve of the style in Style, most items have multiple choices, one has the choice of 0.

I can get the indirect function to work on the first 2, but I get stuck trying to make the third set of data (Sleeve) show up correctly. I have 2 people telling me to do either a VLOOKUP or an INDIRECT(SUBSTITUTE). I am not sure how to do the indirect substitute, or the VLOOKUP properly.
Hers is my data:

[TABLE="class: grid, width: 1200"]
<tbody>[TR]
[TD]Store Category[/TD]
[TD][/TD]
[TD]Style[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sleeve[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store_Category[/TD]
[TD][/TD]
[TD]Sweaters[/TD]
[TD]Sweats_Hoodies[/TD]
[TD]T_Shirts[/TD]
[TD]Tops_Blouses[/TD]
[TD][/TD]
[TD]SweatersSleeve[/TD]
[TD]Sweats_HoodiesSleeve[/TD]
[TD]T_ShirtsSleeve[/TD]
[TD]Tops_BlousesSleeve[/TD]
[/TR]
[TR]
[TD]Sweaters[/TD]
[TD][/TD]
[TD]1/2 Zip[/TD]
[TD]Hoodie[/TD]
[TD]Basic Tee[/TD]
[TD]Blouse[/TD]
[TD][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Sleeveless[/TD]
[TD="align: center"]Sleeveless[/TD]
[TD="align: center"]3/4 Sleeve[/TD]
[/TR]
[TR]
[TD="align: center"]Sweats_Hoodies[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Boat Neck[/TD]
[TD="align: center"]Sweatshirt, Crew[/TD]
[TD="align: center"]Embellished Tee[/TD]
[TD="align: center"]Button Down Shirt[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Short Sleeve[/TD]
[TD="align: center"]Cap Sleeve[/TD]
[TD="align: center"]Batwing, Dolman[/TD]
[/TR]
[TR]
[TD="align: center"]T_Shirts[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Cardigan[/TD]
[TD="align: center"]Track Jacket[/TD]
[TD="align: center"]Graphic Tee[/TD]
[TD="align: center"]Halter[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Long Sleeve[/TD]
[TD="align: center"]Short Sleeve[/TD]
[TD="align: center"]Cap Sleeve[/TD]
[/TR]
[TR]
[TD="align: center"]Tops_Blouses[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Collared[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Personalized Tee[/TD]
[TD="align: center"]Knit Top[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3/4 Sleeve[/TD]
[TD="align: center"]Kimono Sleeve[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Cowl Neck[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Polo Shirt[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Long Sleeve[/TD]
[TD="align: center"]Long Sleeve[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Crewneck[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Tank, Cami[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Off-Shoulder[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Full Zip[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Tunic[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]One Shoulder[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Henley[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Turtle Neck[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Short Sleeve[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Hooded[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Western[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sleeveless[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Keyhole[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Wrap[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Spaghetti Strap[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Kimono[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Strapless[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Poncho[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Scoop Neck[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Shrug[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Sweatercoat[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Tunic[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Turtleneck, Mock[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Twinset[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Vest, Sleeveless[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]V-Neck[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Wrap, Swing[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

What you trying to do that isn't working?

 

[chiguy41]

i cant get the third set of data, Sleeve, to show a drop down list on my tab.
The first drop down would be Store Category (which works), the 2nd is a drop down based on the Style (this works). It is the third drop down isnt working. I need it to read off of drop down 2 (style) and show drop down based on sleeve criteria. Does this make sense?

 

[chiguy41]

would it be better to use the indirect, indirect substitute or a indirect with a vlookup to get the third set of data to show in a drop down?

 

[Andrew Poulsom]

There isn't a relationship between the Style ranges and the Sleeve ranges. The third dropdown should also be based on the first, eg:

=INDIRECT(A1&"Sleeve")

 

[chiguy41]

Ok, I tried that and it works, however......an issue arose. In the second set of data, Style, there is data under Tops_Blouses called Tank, Cami. This set of data needs its own Sleeve in the 3rd set of data.

Please note that the name Tank, Cami cant be put into the 1st set of data as it doesnt belong here.

Any suggestions on this?

Thanks for all the help.

 

[Andrew Poulsom]

What name would you give that range?

 

[L. Howard]

Hi chiguy41,

This seems to do what you want.

I copied the example to a sheet where upper left corner is cell B1.
Row 2 is blank.
Data starts in row 3.
Columns C and H are blank
In B8 I entered "Blank 1" and pulled down to row 24.

In cell N2 I put a data validation drop down > List > selected > B4 to B24 > OK

Select N4 to U4 and while selected type in this formula:

=VLOOKUP(N2,B4:L24,{3,4,5,6,8,9,10,11},0)

Now (while all cells are still selected) Array Enter the formula. Ctrl + Shift + Enter.

Select from the drop down and check the return values.

If you make changes to the FORMULA you must select N4 to U4, make your changes and Array Enter again.

When you replace a "Blank n" with a new Store_Category and fill in the blanks for the new product, styles and sleeves, they will show in the return cells, otherwise it is a 0 (zero).

Howard