VBA -- Changing data based on three conditions?

[jonv]

Ok, I have an excel sheet that has three varible columns. Each column has a dropdown box in it with the different choices. Now, what I want to do, is basically change a formula based on the different combination of these dropdown boxes.

So, lets say I have columns A, B, C with the three dropdown boxes. In column D I have a quantity which I manually input. Then, in column F, what I want to do is basically look at columns A, B, and C, and determine a base cost, then multiply it by column D (the quantity), to get my price for column F.

Does this make sense?

I've been trying to do this in VBA, but I'm not an advanced user of VBA so I do not know all the ways to get things done. But I do understand most logic behind specific lines of code, so I'm not totally oblivious.

Basically, I think what I need is something like this:

If A1 = this, and B1 = this, and C1 = this, then F1 = D1*0.50

The 0.50 will be determined based on the choices of A, B, C. Does this make sense?

To further elaborate. Let me throw an example of the types of things in these dropdown boxes.

EXAMPLE:

A1- B&W, Color
B1- Letter, Ledger
C1- 20#, 80#

So, A1 we will choose whether the print is black and white, or color. B1 will choose whether it is on letter size paper, or ledger. And C1 will choose the weight of the paper. All of which will adjust the cost of each print.

I would greatly appreciate any help or insight to getting this code written. Thanks in advance!

 

[Oaktree]

If I understand correctly, A, B, and C represent the actual orders.

Do you have a table somewhere that tells you how to get to the prices? e.g. a table lists every combination of A/B/C and the corresponding price?

If so, where is that table (or how will arrive at the 0.50 in your example)?

 

[jonv]

The pricing table is not complete yet, but for a sample lets say the following:

On a sheet named "Prices"
We will have four columns: A, B, C, D
A-C are the same as before, and D is a price for each combination.

A, B, C, D
B&W, Letter, 20#, 0.10
Color, Letter, 20#, 0.25
B&W, Ledger, 80#, 0.20
Color, Ledger, 80#, 0.50

Does that help?

 

[Oaktree]

Assuming your price list is unique at the A/B/C level, F1 in your example above could be =D1*SUMPRODUCT(--(Prices!A2:A100=A1),--(Prices!B2:B100=B1),--(Prices!C2:C100=C1),Prices!D2:D100)

 

[jonv]

Well, this doesnt solve the fourth variable of quantity that will be manually typed.

Also, let me note that this is not going to be used as a method of keeping track of all orders. It will simply be used to input the clients quantity, and quickly give them a rough estimate of the cost. Thus we will only be dealing with the firt two rows. Row 1 being the headers, and row 2 being the variables.

So basically, we're going to have four variables. One which is manually typed in the D column, and the other three that are chosen from the dropdown boxes in columns A-C.

Is there anyway to embed into a VBA module the combinations and what their multiplier would be?

 

[Oaktree]

Well, this doesnt solve the fourth variable of quantity that will be manually typed.

Sure it does! The formula starts with =D1*SUMPRODUCT...

The SUMPRODUCT portion would look up the cost and then multiply by the quantity you have in D1.

You *could* put the combinations/multipliers in VBA, but why? That would greatly reduce the visibility of what's going on. My two cents is that you'd want to take advantage of the spreadsheet's power and make it easy for someone to find (or change) one of the inputs, as prices are sure to change.