Excel 2007 : Help populating cells according to combinations

**jenna@telstrashop** · 03-18-2010, 07:26 PM

Hello,

I am trying to find out whether it is possible to get a certain result from the combined values of a number of other cells that have drop downs. Obviously you can only pick certain amounts from the drop downs, but different combinations would come up with differen results. Eg:

Options for Column A: 12/24 Months
Options for Column B: 5/10/29/39/59 Data pack
Options for Column C: New/Upgrade

Column D: Should automatically reflect the amount of points according to the combination of above options

Column A Column B Column C Column D
12 29 New 20 points
24 10 Upgrade 15 points

Does anyone know if this is possible?

Would greatly appreciate any help.

Kind Regards,
Jenna

**shg** · 03-18-2010, 07:37 PM

Welcome to the forum, jenna.

See http://www.excelforum.com/excel-prog...mbination.html and http://www.excelforum.com/excel-work...n-formula.html. Post back if you can't figure out how to plug in your data.

**Palmetto** · 03-18-2010, 07:41 PM

Does anyone know if this is possible?

Probably, but you haven't fully explained the logic of how points would be calculated.

If you upload a sample workbook that meets the following criteria, a precise answer may be possible. The sample workbook should:

1. EXACTLY duplicate the structure of your real workbook
2. Contain representative but non-sensitive dummy data
3. Shows several examples of the desired results with sufficient explanation of the logic

One possibility is to use a look up table that matches selections to predetermined values.

**shg** · 03-18-2010, 07:59 PM

I'm shutting down, so I'll post this. Dunno what you want in col D.

**jenna@telstrashop** · 03-21-2010, 11:39 PM

Thank you for the responses and sorry for the late reply. I'm obviously a bit out of my depth but I compiled a simple document that hopefully should make things a bit clearer.

We have a retail business and basically we just want to set up a reward system for the guys in the shop. If you have a look at Sheet 2 of the workbook it has the lists for the drop downs that are used on Sheet 1 and next to it is a table that shows how the points are allocated according to the product, length of the sales contract and whether it's a new sale or an upgrade.

I would like the salesmen to be able to enter the data into columns A, B and C and have column D be automatically populated with the correct amount of points according to that combination.

Thank you in advanvce!

**Palmetto** · 03-22-2010, 07:43 AM

See attached. Had to fix a couple of named ranges and the Data validation (which you will need to correct in your real workbook)

A named range was created for the points table and the LOOKUP function nested within VLOOKUP to determine which column to return the values.

=VLOOKUP(A3,Points_Table,LOOKUP(LEFT(B3,1)&C3,{"N12","N24","U12","U24"},{1,2,3,4})+1,FALSE)

**jenna@telstrashop** · 03-23-2010, 10:58 PM

Thankyou!
I would like to see how the nested part comes about, I'm a bit fuzzy as to how you did that, but I'll check it out in google and see if I can gain a better understanding if I need to use it in the future.
Very interesting.
Thanks again,
Jenna