3 droplist to have different fomulas acording to choice

JaykSA

New Member
Joined
Oct 11, 2014
Messages
18
i have 3 cells (all droplist)
Outerborder
  • wood=$30
  • polystirene=$50
  • clay=$60
(WIth out showing the numbers)
CONTENT
  • Filling=30%
  • Fake=0
  • wood=10%


SIZE
  • 500mmx250mm=(*5)
  • 450mmx250mm=(*4)
  • 330mmx250mm=(*3)
all of them should work together to evaluate a price
in a nother cell
Any ideas???
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Like so:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">OuterBorder</td><td style=";">Content</td><td style=";">Size</td><td style=";">Price</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #574123;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #574123;background-color: #FFFF00;;">wood</td><td style="color: #574123;background-color: #FFC000;;">Filling</td><td style="color: #574123;background-color: #00B0F0;;">500mmx250mm</td><td style="text-align: right;;"> $ 195.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #574123;background-color: #FFFF00;;">wood</td><td style="text-align: right;background-color: #FFFF00;;">$30 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #574123;background-color: #FFFF00;;">polystirene</td><td style="color: #574123;background-color: #FFC000;;">wood</td><td style="color: #574123;background-color: #00B0F0;;">450mmx250mm</td><td style="text-align: right;;"> $ 6,200.00 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #574123;background-color: #FFFF00;;">polystirene</td><td style="text-align: right;background-color: #FFFF00;;">$50 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #574123;background-color: #FFFF00;;"></td><td style="color: #574123;background-color: #FFC000;;"></td><td style="color: #574123;background-color: #00B0F0;;"></td><td style=";"> n/a </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #574123;background-color: #FFFF00;;">clay</td><td style="text-align: right;background-color: #FFFF00;;">$60 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #574123;background-color: #FFFF00;;"></td><td style="color: #574123;background-color: #FFC000;;"></td><td style="color: #574123;background-color: #00B0F0;;"></td><td style=";"> n/a </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #574123;background-color: #FFC000;;">Filling</td><td style="text-align: right;background-color: #FFC000;;">30%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="color: #574123;background-color: #FFFF00;;"></td><td style="color: #574123;background-color: #FFC000;;"></td><td style="color: #574123;background-color: #00B0F0;;"></td><td style=";"> n/a </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #574123;background-color: #FFC000;;">Fake</td><td style="text-align: right;background-color: #FFC000;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="color: #574123;background-color: #FFFF00;;"></td><td style="color: #574123;background-color: #FFC000;;"></td><td style="color: #574123;background-color: #00B0F0;;"></td><td style=";"> n/a </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #574123;background-color: #FFC000;;">wood</td><td style="text-align: right;background-color: #FFC000;;">10%</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #574123;background-color: #FFFF00;;"></td><td style="color: #574123;background-color: #FFC000;;"></td><td style="color: #574123;background-color: #00B0F0;;"></td><td style=";"> n/a </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #574123;background-color: #00B0F0;;">500mmx250mm</td><td style="text-align: right;background-color: #00B0F0;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="color: #574123;background-color: #FFFF00;;"></td><td style="color: #574123;background-color: #FFC000;;"></td><td style="color: #574123;background-color: #00B0F0;;"></td><td style=";"> n/a </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #574123;background-color: #00B0F0;;">450mmx250mm</td><td style="text-align: right;background-color: #00B0F0;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="color: #574123;background-color: #FFFF00;;"></td><td style="color: #574123;background-color: #FFC000;;"></td><td style="color: #574123;background-color: #00B0F0;;"></td><td style=";"> n/a </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="color: #574123;background-color: #00B0F0;;">330mmx250mm</td><td style="text-align: right;background-color: #00B0F0;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="color: #574123;background-color: #FFFF00;;"></td><td style="color: #574123;background-color: #FFC000;;"></td><td style="color: #574123;background-color: #00B0F0;;"></td><td style=";"> n/a </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="color: #574123;background-color: #FFFF00;;"></td><td style="color: #574123;background-color: #FFC000;;"></td><td style="color: #574123;background-color: #00B0F0;;"></td><td style=";"> n/a </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="color: #574123;background-color: #FFFF00;;"></td><td style="color: #574123;background-color: #FFC000;;"></td><td style="color: #574123;background-color: #00B0F0;;"></td><td style=";"> n/a </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">B2, $H$2:$I$10, 2, 0</font>)*(<font color="Red">1+VLOOKUP(<font color="Green">C2, $H$2:$I$10, 2, 0</font>)</font>) * VLOOKUP(<font color="Red">D2, $H$2:$I$10, 2, 0</font>), "n/a"</font>)</td></tr></tbody></table></td></tr></table><br />
 

JaykSA

New Member
Joined
Oct 11, 2014
Messages
18

ADVERTISEMENT

Hi Jerry! sorry i am clueless to what I am doing wrong.

You formula works mine does nothing.

=IFERROR(VLOOKUP(C34,$X$35:$Y$82,34,0)*(1+VLOOKUP(E34,$X$35:$Y$82,34,0))*VLOOKUP(G34,$X$35:$Y$82,34,0),"N/a")

Set them all up as a droplist

I am not to clued up. For someone that now how this works could you tell me where i went wrong?
Sorry for all the questions
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
There are only 2 columns in the search range, X:Y. The third parameter of a VLOOKUP is the column number in the designated range, since your range only has 2 columns, the highest number would be 2 for the third parameter, but you put 34 for some reason. Try 2 instead, 2 would bring back the answer from the 2nd column in the designated search range.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,986
Messages
5,526,065
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top