Combining/Adding Info into cells from choices in drop downmenu's.

**BoardGirl** · 05-10-2010, 03:11 AM

Hi Guys,

I've spent 2 days trying to figure out how to do this!

Basically I have created 3 Drop Down menu's in my excel sheet. From here I need to have specific information placed in different cells depending on what combination of words are chosen in have in my drop down menu. Is this possible?

I have tried to use IF's but have failed, #VALUE! everytime! I've got no idea what else to try.

I have attached my workings so far. As you will see I have the 3 Drop Down menu's, (GARMENT, RANGE, DESIGN) In another worksheet I have our range with all the relevant information on our garments. Price, Printing Options, Embroidery, Colour Range etc. This is where I need to pull the information from top place into the relevant cells, and add prices to the correct boxes.

So for example: I have chosen "HOODIE" from my GARMENT drop down menu, "FRESHMAN" from my RANGE drop down menu and "DESIGN 2" from my DESIGN menu. Which all refer back to a specific garment in my "range worksheet" That would mean under GARMENT DECORATION "P1 COLOURS" and P2 COLOURS (Meaning PRINT POSITION 1/2 amount of colours), I need it to say from the "range worksheet" 2 in both of those boxes. From there I will insert the quantity and specific sizes and it then needs to come up with a price per garment which is also in the "range worksheet".

More complex is that I need to be able to input the correct sizes and it will calculate the amount of material needed for each size, so that will in the end change the final cost price. But I might tackle this bit once I get the 1st problem sorted.

Thanks in advance!

Cheers
BoardGirl

**pike** · 05-10-2010, 04:45 AM

BoardGirl

could you zip the file so i can have a look?

**BoardGirl** · 05-10-2010, 06:52 PM

Thanks for reviewing it. I have attached the file in Zipped Folder. Cheers

**smz** · 05-11-2010, 01:34 AM

Hi Boardgirl,

I've taken a crack at it (see attached.)

Here's the summary of what I've done:

Added a new "Key" column in the "Ranges" sheet, so we can easily pull up the data we need.
Defined "Data" as the name to refer to the "Ranges" sheet data.
Added a quick check, assuming 50/100/250 are the allowable quantities.
Using the garment/range/design as search keys, grab the appropriate "Sell Price" based on the quantities.

Hope this helps a little. =)

Understand pike is also helping and may offer a more effective and versatile solution.

**BoardGirl** · 05-11-2010, 02:01 AM

Thanks smz for your help. I can see now that it can actually work! It's made my day!

With the quick check for the allowable quantities, we are allowing other quantities the 50, 100 and 250 are just our price points (where if you order that many it will be slightly cheaper). Is there an easy way to take that off so we can still get a price for the other quantities, but still have the specific price if the quantity ordered is between 50-100, 100-250 and 250+ . I have tried to just delete it, but I get #N/A in the price when i change the amount in any of the size boxes. Thanks again!

**smz** · 05-11-2010, 02:25 AM

Hi again Boardgirl,

Attached is v2.0 of the same.

Edits:

Added two new columns (O and S) in "Range" - you'll need to redo the cost prices. I just entered a dummy value in O11 and S11.
Re-did the formula to suit.

I wasn't sure whether it's the UNIT PRICE or SELL PRICE you wanted to get.
Currently the formula (cell D31 in "Price Calculator") is set to SELL PRICE. If you want the other, just rename that bit to "UNIT PRICE" and it'll grab the correct one.

Cheerio!

**BoardGirl** · 05-11-2010, 03:02 AM

Thanks, It is the Sell price that we need to show here, so it's all good! Thank-you so much

Now i've got to figure out how to include the printing position/colours and embroidery into the sheet. Would a similar formula work for the specific numbers (that are on the "Range" page - Printing Positions/Printing Colours) that need to be placed with each different "range" choice - or is there an easier way to do this?

Cheers

**smz** · 05-11-2010, 07:24 PM

Hi Boardgirl,

You're most welcome. Technically the issue has been solved so now we're venturing outside the domain of the original post.
In response to your question, I can say those additional requirements are possible with the following hints/tips:

Keep your "data" (i.e. the stuff in the "Range" sheet) as 'raw' as possible... i.e. keep it well organised/categorised.
Every column title for the "data" should be unique. So rather than having multiple "UNIT PRICE" headers, have something like "UNIT50", "UNIT100", "UNITX".
Every row should have a unique key (hence the "Key" column).
Now that you have unique row & column identifiers you can use VLOOKUP and MATCH to get the right data you need.
Thing to consider - if your clients are meant to fill the front sheet in, it'll be a good idea not to show sensitive information, eg the difference between unit price and sell price.

All the best!