Auto-Calculate price based on width x height

Use vlookup or index/match

alternatively you might be able to generate a simple formula using an 'if / then' statement. Can you share the graph in your book or provide a range of sample dimentions with prices?

Welcome to the Board GuybrushThreep

Without provideing how your data is laid out, leads to alot of speculation.

You can download one of the tool listed at the bottom of my reply to do so like I am doing.




This is just a guess on your layout. Hope it helps.

Yes that sheet is effectively what I'm referring to. Ideally it would be hidden, and the only functional items visible would be the input fields.

I'm not sure how to post a spreadsheet here, but the function would be like this:

step 1) Its all blank and data is yet to be entered

Item#|Width|Height| Price
1

step 2) You enter your width and height

Item#|Width|Height|Price
1 | 1500 | 900 |

step 3) As soon as you hit "enter" it goes to the next item, and a price was automatically selected for the first item

Item#|Width|Height|Price
1 | 1500 | 900 | $20
2

I'm not sure how to post a spreadsheet here

Click to expand...

You can not post a spreadsheet. But you can post a small sample of your data layout and your expected results as I stated above.

You need to download one of the two addins listed at the bottom of my reply.

So from what I'm gathering, you have your Data on a one sheet, and you want to input the dimensions on a seperate sheet and come up with the price. Something like this:

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">Item #</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">Width</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">Height</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">Price</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">1500mm</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">900mm</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">$20.00</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I have a tendency to try and read minds and help out new members.

But I (we) really need your input and your actual Data to work out the best solution. Sometimes I get way over my head because I am not all that good at solving the issue.

Try an download one of the programs, show a few rows of your data, and a few rows of what your results should look like.

Here is an example price list:

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 84px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="text-align: center; font-weight: bold;" colspan="5">Window Prices (Height x Width)</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="font-weight: bold;">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: right; background-color: rgb(153, 204, 255); font-style: italic; font-weight: bold;">mm</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">1000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">2000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">3000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">4000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">5000</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">500</td> <td style="text-align: right;">$20.00</td> <td style="text-align: right;">$30.00</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$60.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">600</td> <td style="text-align: right;">$30.00</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$60.00</td> <td style="text-align: right;">$70.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">700</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$55.00</td> <td style="text-align: right;">$70.00</td> <td style="text-align: right;">$80.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">800</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$60.00</td> <td style="text-align: right;">$70.00</td> <td style="text-align: right;">$90.00</td> <td style="text-align: right;">$100.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">900</td> <td style="text-align: right;">$60.00</td> <td style="text-align: right;">$80.00</td> <td style="text-align: right;">$100.00</td> <td style="text-align: right;">$120.00</td> <td style="text-align: right;">$160.00</td></tr> </tbody></table>

Here is the desired FRONT END:

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 84px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 92px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> </tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td style="text-align: center; font-weight: bold;" colspan="3">Please enter all windows:</td> <td>
</td> </tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Item #</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Width</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Height</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Price</td> </tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td style="text-align: center; font-style: italic; font-weight: bold;">1</td> <td>
</td> <td>
</td> <td>
</td> </tr></tbody></table>

So, after entering the width and height, the price is automatically found:

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 84px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 92px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">25</td> <td style="text-align: center; font-weight: bold;" colspan="3">Please enter all windows:</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">26</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right; background-color: rgb(153, 204, 255); font-style: italic; font-weight: bold;">mm</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">1000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">2000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">3000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">4000</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">5000</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">27</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Item #</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Width</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Height</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Price</td> <td>
</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">500</td> <td style="text-align: right;">$20.00</td> <td style="text-align: right;">$30.00</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$60.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">28</td> <td style="text-align: center; font-style: italic; font-weight: bold;">1</td> <td style="text-align: center;">2000</td> <td style="text-align: center;">800</td> <td>
</td> <td>
</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">600</td> <td style="text-align: right;">$30.00</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$60.00</td> <td style="text-align: right;">$70.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">29</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">700</td> <td style="text-align: right;">$40.00</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right;">$55.00</td> <td style="text-align: right;">$70.00</td> <td style="text-align: right;">$80.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">30</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">800</td> <td style="text-align: right;">$50.00</td> <td style="text-align: right; color: rgb(255, 0, 0); font-weight: bold;">$60.00</td> <td style="text-align: right;">$70.00</td> <td style="text-align: right;">$90.00</td> <td style="text-align: right;">$100.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">31</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right; background-color: rgb(204, 204, 255); font-weight: bold;">900</td> <td style="text-align: right;">$60.00</td> <td style="text-align: right;">$80.00</td> <td style="text-align: right;">$100.00</td> <td style="text-align: right;">$120.00</td> <td style="text-align: right;">$160.00</td></tr></tbody></table>

From here you should be able to enter as many times as you like...

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 84px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">37</td> <td style="text-align: center; font-weight: bold;" colspan="3">Please enter all windows:</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">38</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">39</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Item #</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Width</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Height</td> <td style="text-align: center; background-color: rgb(204, 204, 255); font-weight: bold;">Price</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">40</td> <td style="text-align: center; font-style: italic; font-weight: bold;">1</td> <td style="text-align: center;">2000</td> <td style="text-align: center;">800</td> <td style="text-align: right;">$60.00</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">41</td> <td style="text-align: center; font-style: italic; font-weight: bold;">2</td> <td style="font-style: italic;">
</td> <td>
</td> <td>
</td></tr></tbody></table>

So the problem is how to determine the price based on the two separate dimensions (width & height)???

If you want everything on one sheet:


Excel 2007


If you want your lookup data on a different sheet:


Excel 2007

Hey thats brilliant! I was thinking I'll have to create macros and such, but this is a perfect solution.

Many Many Thanks to all!

Glad it worked. Thanks for the feedback.