VBA -- Changing data based on three conditions?

jonv

New Member
Joined
Nov 3, 2005
Messages
22
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,015
Office Version
  1. 365
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

New Member
Joined
Nov 3, 2005
Messages
22
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,015
Office Version
  1. 365
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

New Member
Joined
Nov 3, 2005
Messages
22
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,015
Office Version
  1. 365
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.
 

Forum statistics

Threads
1,141,097
Messages
5,704,314
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top