Need help referencing b/w sheets for school project
 

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

Hello and welcome, Pat.

Any chance you could upload a dummy workbook rather than the images?
Makes it far easier to examine the workings of what you have so far and come up with the best solution for you.

Cheers.

S.

I've attached a working document. Thank you for your help.

Is this anything like what you're after?