Drop-down list connected to multiple formulas

**m_buskirk** · 12-16-2011, 12:41 PM

Hey everyone. I am trying to figure out how to create a drop down list that is connected to multiple formulas. The basic way it will work is the far left column will have the drop-down list. There will be 6 columns to the right and every column will have a different formula. Every selection in the drop-down list will use a different set of formulas. Does anybody know how to do this? Let me know if I didn't explain this very well. Thanks.

**Richard Buttrey** · 12-16-2011, 12:46 PM

Hi,

I can do no better than to refer you to Debra Dalgelish's web site page http://www.contextures.com/xlDataVal02.html
where you'll see the definitive answer as to how to create dependent drop down lists.

Regards

**Fotis1991** · 12-16-2011, 12:50 PM

Ηι m_buskirk

How many sets of formulas for eatch column?

How many items in drop-down list??

**m_buskirk** · 12-16-2011, 01:00 PM

This is very close to what I want, but I don't want multiple lists. I want to be able to select an item from the drop down list then have a the numerical formulas in each column to the right change based on my list selection.

For example:

A1 will have a fertilizer grade of 10-34-0 which would be from a list. A2 will be where I insert a number. A3 will be =A2*0.1. A4 will be =A2*0.34.

If I chose 32-0-0 from A1, then A3 would be =A2*0.32.

**m_buskirk** · 12-16-2011, 01:05 PM

The list would have about 8 entries at the most. Each column will have 5 formulas at the most. However, depending on the selection from the list, some columns won't use a formula everytime as they will just need to equal zero.

**Fotis1991** · 12-16-2011, 01:19 PM

A1 will have a fertilizer grade of 10-34-0 which would be from a list. A2 will be where I insert a number. A3 will be =A2*0.1. A4 will be =A2*0.34.

If I chose 32-0-0 from A1, then A3 would be =A2*0.32.

So you will use all the formulas, in Column A???

**m_buskirk** · 12-16-2011, 02:09 PM

No I am sorry. That should have been A1, B1, C1, etc.

A1 will have a fertilizer grade of 10-34-0 which would be from a list. B1 will be where I insert a number. C1 will be =B1*0.1. D1 will be =B1*0.34.

If I chose 32-0-0 from A1, then C1 would be =B1*0.32.

**Haseeb Avarakkan** · 12-16-2011, 06:58 PM

Looks like VLOOKUP will do it.

K1 to down enter your all grades like, 10-34-0 etc...
L1 to down enter the values for the 1st columns. means If you select this grade what value should multiply with B1
M1 to down enter the values for 2nd columns. Add all columns how many you have.
If you want to show zero for some grades enter 0 in the correspondent column in the grad

Then use in C1,

=B1*VLOOKUP($A1,$K:$Z,COLUMNS($C1:C1),0)

If you can attach a dummy file, would be helpful.

**Fotis1991** · 12-17-2011, 05:03 AM

Hi

Also a possiple solution, is to name two ranges(for eatch grade>>a value)for columns c&d and then in the columns C & D to use round & vlookup functions.

Hope to helps you.