If statement, need solution

Tommy753

New Member
Joined
Jul 25, 2017
Messages
9
[Use drop down selecting Quartz, Granite or solid surface (Drop down created) Cause price on left to populate price cell. Help would be appreciate finding a formula
Quartz Drop down select type
Quartz Price $$$$$

Granite Price $$$$

Solid Surface Price $$$$
Copy to PriceCell $$$

<tbody>
</tbody>
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

You mean something like this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Material</td><td style=";">Price</td><td style="text-align: right;;"></td><td style=";">Material</td><td style=";">Granite</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Quartz</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style=";">Price</td><td style="text-align: right;;">150</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Granite</td><td style="text-align: right;;">150</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Solid Surface</td><td style="text-align: right;;">75</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet169</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">E1,$A$2:$B$4,2,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

Tommy753

New Member
Joined
Jul 25, 2017
Messages
9
Thank you, With E1 being a drop down to select. When I get in front of my computer I will apply and let you know...
 

Tommy753

New Member
Joined
Jul 25, 2017
Messages
9
I was thinking more an If statement because, from your example:

Lets make E1 a Data Validation drop-down with Select, Quartz, Granite, Solid Surface

When Quartz is current in E1, then populate the value in B2 or $100 into E2
When Granite is Current in E1, then populate the value in B3 or $150 into E2
and so on with Solid surface...

Only need cells E1(drop-down), B2,3,4 and result in E2 Where value in E1 causes data in either B2,B3 or B4 to be moved to E2.

Note: Price for each of the surfaces are being calculate from a workbook in another tab and that value is being referred to the B1-3 cells.

I hope that makes my goal a little clearer Thanks, Tommy
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
For what you're describing, VLOOKUP is the proper function to use rather than a Nested IF.

Are you saying the formula provided is Not working?
If the formula is Not giving you the correct results, it would most likely mean the Values/Texts in your drop down does Not match the Values/Texts in the Table (Column A), check for leading and or trailing Space(s) in both.
 

Tommy753

New Member
Joined
Jul 25, 2017
Messages
9
I looked closely at the formula and I even created a new scenario using simple references and it just continue to return na I'm certain my references were correct
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Can you upload a sample file to a free file host like Dropbox and post the link here.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Ok,

There a few problems here:

1. Your data is Horizontal, Not Vertical as in your OP and description.
2. Your Data Validation list Does Not match the Table F38, H38, and L38.
3. You have a lot of merged cells, which could cause problems, but for this matter, it's Ok.

Solution:

Change the DV list to Match F38, H38, and L38
Use HLOOKUP instead of VLOOKUP

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">38</td><td style="font-weight: bold;text-align: center;;">Quartz F207</td><td style=";"></td><td style="font-weight: bold;text-align: center;;">Granite H207</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";"></td><td style="font-weight: bold;text-align: center;;">Solid Surface L207</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style="border-right: 1px solid black;;"></td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;">Quartz F207</td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">39</td><td style="font-weight: bold;text-align: center;background-color: #FFFFFF;;"> $ 3,250.00 </td><td style="font-weight: bold;text-align: right;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFFFF;;"> $ 2,300.00 </td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFFFF;;"></td><td style="font-weight: bold;background-color: #FFFFFF;;"> $ 1,750.00 </td><td style="background-color: #FFFFFF;;"></td><td style="background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">Countertop Price</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><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10%</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #009900;;">$3,250.00</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #009900;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;color: #009900;;"></td></tr></tbody></table><p style="width:8.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Price Sheet</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">Y39</th><td style="text-align:left">=HLOOKUP(<font color="Blue">Y38,F38:L39,2,0</font>)</td></tr></tbody></table></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>AA</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">75</td><td style=";">Select</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">76</td><td style=";">Quartz F207</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">77</td><td style=";">Granite H207</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">78</td><td style=";">Solid Surface L207</td></tr></tbody></table><p style="width:8.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Price Sheet</p><br /><br />

PS: you should have also clarified which cell the formula was supposed to be in, I had to look thru a bunch of cells to figure it out (cause it's not E1).
 
Last edited:

Tommy753

New Member
Joined
Jul 25, 2017
Messages
9
Thank you for your help! I was finally able to get it to work.

When selecting the data field I was incorrectly selecting the header field not both the header and the data fields together.

Once I figured that out, worked like a charm...
 

Forum statistics

Threads
1,081,556
Messages
5,359,554
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top