Create Drop Down Menu ORDER FORM with Variations

**chosen** · 11-05-2017, 08:36 AM

Hi guys,

I'm trying to do a order form for my clients in Google Sheets and I came across a problem..
Basically this is for a stock of T-shirts and Hoodies I have available in a range of colours and sizes.

Example

STOCK:
T-SHIRT - Black (S,M,L,XL), White (S,M,L,XL)
HOODIE - WHITE (S,M), YELLOW (S,M)

I want the order form to find the available options for the specific product. When you Choose from drop down menu T-SHIRT in the next cell where you have COLOUR there will be available options BLACK and WHITE.

I did managed to do it with all possible variations but then you have an endless list to choose from. So there needs to be another solution for this.

Capture.PNG
Capture2.PNG

Any help will be much appreciated!

**JohnTopley** · 11-05-2017, 08:51 AM

Look here ...

http://www.contextures.com/xlDataVal02.html

Give it a go!

**chosen** · 11-05-2017, 09:15 AM

Hi,

Thanks for this but I need a more dynamic drop down list where the data will be read from a separate sheet.

**JohnTopley** · 11-05-2017, 09:52 AM

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**JohnTopley** · 11-05-2017, 10:29 AM

See attached

Dynamic lists created using formula ..

=IFERROR(INDEX($F$2:$F$10,SMALL(IF(INDEX($G$2:$I$10,,MATCH(Sheet1!$A$2,Sheet2!$G$1:$I$1,0))="Y",ROW(Sheet2!$F$2:$F$10)-ROW($F$2)+1,""),ROWS($F$2:F2))),"")

Enter with CTRL+SHIFT+ENTER

Named lists using formula

name: ColourList

Refers to: =OFFSET(Sheet2!$J$2,,,COUNTIF(Sheet2!$J$2:$J$100,"?*"),1)

See Option 2

There is a VBA routine which identifies which row in "Garment" column has been selected from the Drop down, places the garment ID in "Z1" and the formulae create DV lists based on this selection.