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:

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

<tbody>
</tbody>

 

[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