Originally Posted by patW

I have two sheets, sheet 1 is a order form and sheet 2 is the reference of products and prices.

Sheet1: http://cl.ly/2A1b280K3x0d0o1F3y3x

Sheet 2: http://cl.ly/1k1F3M0C2l3S1s2o1K3W

Sheet 1 has drop-downs for Color1, Color2, Charm1, Charm2 referenced from sheet 2.

How can I:
1. In sheet 1, how I can I have Group Number column populate with the listed group # 0001 and an order number starting at 1 each time a choice from Color1 drop down is selected.
i.e. 00001 1 Royal Blue
00001 2 Royal Blue
00001 3 Navy Blue

2. Also, after a choice is selected from Color1 drop down the Group Retail column is populated with the base price listed in sheet 2, of $19.99

---AND---

3. When and/if additional Charm2 is selected, an additional $4.00 is added to Group Retail column for that row.

Basically, I'd like a way to populate this order form with subsequent order #'s (1-500) each time Color1 is selected from the table and populate the Group Retail column with $19.99 until the additional Charm2 (of $4.00) is added to the order making that rows Group retail total $23.99

Any help is appreciated.
Cheers, Pat