V Look Up Help

V

Vamplew

New Member
Joined
Nov 9, 2011
Messages
38
In a invoice I have a stock list at the side for personal reference. Next to the stock list is the amount of stock I have left of each item.
I need a formula please which every time I put a number in to the the no column it deducts its from the stock avalibilty and matches it up with the right item. ALso I need it to tell me when I have none available.
Functions I think that are need are If and VLOOkUP.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Coudl you post your data here?


To post your data you can download and install two of the following programs:

HTLMaker

or
Excel Jeanie

or when using Internet Explorer just put a borders around your data in Excel and copy those cells into your post.
 
Upvote 0
<TABLE style="WIDTH: 786pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1048 x:str><COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 209pt; mso-width-source: userset; mso-width-alt: 10203" width=279><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7862" width=215><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 195pt; mso-width-source: userset; mso-width-alt: 9508" width=260><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: red; WIDTH: 54pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl28 height=17 width=72>No</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; WIDTH: 209pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl29 width=279>Description</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: lime; WIDTH: 161pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl30 width=215>Item Cost</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: yellow; WIDTH: 71pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl32 width=94>Total Cost</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: red; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl36 width=64> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #00ccff; WIDTH: 195pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl34 width=260> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #00ccff; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl35 width=64 align=right x:num="0">£0.00</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: red; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl28 height=18 align=right x:num>0</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl29> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: lime; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl31 align=right x:num="0">£0.00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl33 align=right x:num="0">£0.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: red; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl36> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #00ccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl34>1 Pint Fresly Turned Caster</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #00ccff; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl35 align=right x:num="2">£2.00</TD></TR></TBODY></TABLE>
 
Upvote 0
Will you add a new line each time a new orders is coming in or do you want to deduct from the same line?
In the second case you need a macro not a VLOOKUP solution.

This is maybe not what you after but try:
Sheet1(when stock is more than 50)
Excel Workbook
ABCDEFGHIJKL
1Amount Customer WantsItem DescriptionCost of ItemTotal CostItem Description in StocklistPrice
2NoDescriptionItem CostTotal CostStock AvailibilityStock left
32Pint Fresly Turned Caster2.004.005014Pint Fresly Turned Caster2.00
43Pint Fresly Turned Caster2.006.00
53Pint Fresly Turned Caster2.006.00
66Pint Fresly Turned Caster2.0012.00
Sheet


When stock is less that 50:

Excel Workbook
ABCDEFGHIJKL
1Amount Customer WantsItem DescriptionCost of ItemTotal CostItem Description in StocklistPrice
2NoDescriptionItem CostTotal CostStock AvailibilityStock left
32Pint Fresly Turned Caster2.004.0050None in stockPint Fresly Turned Caster2.00
43Pint Fresly Turned Caster2.006.00
53Pint Fresly Turned Caster2.006.00
66Pint Fresly Turned Caster2.0012.00
739Pint Fresly Turned Caster
Sheet1


You need to add conditional format to cell J3 to make this cell Red if amount of stock is less than 50.


Will this help you?
 
Upvote 0
That ooked about right but I have hid some of my columns here is an easier version to understand.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 68px"><COL style="WIDTH: 279px"><COL style="WIDTH: 215px"><COL style="WIDTH: 94px"><COL style="WIDTH: 64px"><COL style="WIDTH: 288px"><COL style="WIDTH: 64px"><COL style="WIDTH: 118px"><COL style="WIDTH: 86px"></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>E</TD><TD>F</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">Stock Availibility</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">Stock Left</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">No</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Description</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">Item Cost</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Total Cost</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">0</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">1 Pint Fresly Turned Caster</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£2.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">20</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">20</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E19</TD><TD>=VLOOKUP(B19,$M$18:$N$32,2)</TD></TR><TR><TD>F19</TD><TD>=A19*E19</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
This will help you I think.
 
Upvote 0
Here is the whole table and stocklist

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>E</TD><TD>F</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">Stock Availibility</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">Stock Left</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">No</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Description</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">Item Cost</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Total Cost</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">0</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">1 Pint Fresly Turned Caster</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£2.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">20</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">20</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">1/2 Pint Red Maggots</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£0.60</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">50</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">50</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">1KG Worms</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£15.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">25</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">25</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">6mm Miracle Baits Snowball Pellets</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£3.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">12</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Bag of KingFisherTackle 4mm Feed Pellet</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£2.50</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">15</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">15</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Discorger</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£0.50</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">15</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">15</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Garbolino DC2 Floats</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£3.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">17</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">17</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Maver Mxi Series 5 Attachments</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£50.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">2</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Maver Mxi Series 5 SeatBox</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£350.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">2</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Maver Umbrellas</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£20.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">5</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Middy Stink Bag and Keep Net Pack</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£30.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">2</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Miracle Paste 500g</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£2.50</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">8</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Preston Size 16 Barbed Hooks Xtra Strong</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">£2.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">5</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E19</TD><TD>=VLOOKUP(B19,$M$18:$N$32,2)</TD></TR><TR><TD>F19</TD><TD>=A19*E19</TD></TR><TR><TD>E20</TD><TD>=VLOOKUP(B20,$M$18:$N$32,2)</TD></TR><TR><TD>F20</TD><TD>=A20*E20</TD></TR><TR><TD>E21</TD><TD>=VLOOKUP(B21,$M$18:$N$32,2)</TD></TR><TR><TD>F21</TD><TD>=A21*E21</TD></TR><TR><TD>E22</TD><TD>=VLOOKUP(B22,$M$18:$N$32,2)</TD></TR><TR><TD>F22</TD><TD>=A22*E22</TD></TR><TR><TD>E23</TD><TD>=VLOOKUP(B23,$M$18:$N$32,2)</TD></TR><TR><TD>F23</TD><TD>=A23*E23</TD></TR><TR><TD>E24</TD><TD>=VLOOKUP(B24,$M$18:$N$32,2)</TD></TR><TR><TD>F24</TD><TD>=A24*E24</TD></TR><TR><TD>E25</TD><TD>=VLOOKUP(B25,$M$18:$N$32,2)</TD></TR><TR><TD>F25</TD><TD>=A25*E25</TD></TR><TR><TD>E26</TD><TD>=VLOOKUP(B26,$M$18:$N$32,2)</TD></TR><TR><TD>F26</TD><TD>=A26*E26</TD></TR><TR><TD>E27</TD><TD>=VLOOKUP(B27,$M$18:$N$32,2)</TD></TR><TR><TD>F27</TD><TD>=A27*E27</TD></TR><TR><TD>E28</TD><TD>=VLOOKUP(B28,$M$18:$N$32,2)</TD></TR><TR><TD>F28</TD><TD>=A28*E28</TD></TR><TR><TD>E29</TD><TD>=VLOOKUP(B29,$M$18:$N$32,2)</TD></TR><TR><TD>F29</TD><TD>=A29*E29</TD></TR><TR><TD>E30</TD><TD>=VLOOKUP(B30,$M$18:$N$32,2)</TD></TR><TR><TD>F30</TD><TD>=A30*E30</TD></TR><TR><TD>E31</TD><TD>=VLOOKUP(B31,$M$18:$N$32,2)</TD></TR><TR><TD>F31</TD><TD>=A31*E31</TD></TR><TR><TD>E32</TD><TD>=VLOOKUP(B32,$M$18:$N$32,2)</TD></TR><TR><TD>F32</TD><TD>=A32*E32</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #0000ff; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #0000ff; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #0000ff; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #0000ff"><TBODY><TR><TD>Data Validation in Spreadsheet</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Allow</TD><TD>Datas</TD><TD>Input 1</TD><TD>Input 2</TD></TR><TR><TD>B19</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B20</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B21</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B22</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B23</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B24</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B25</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B26</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B27</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B28</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B29</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B30</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B31</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR><TR><TD>B32</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
I have sorted it now that was to show you it in Excel Genie. Now I need a V Look Up I think to NAme to the right stock availibility. Because now if I was to Put 20 Casters in A28 it deducts the stock from P28 not the casters row which is P19. I have shown the formula for the last thing.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>E</TD><TD>F</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff6600"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #ff0000">XXX</TD><TD style="BACKGROUND-COLOR: #ff0000">Start Stock</TD><TD style="BACKGROUND-COLOR: #ff0000">Stock Left</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">No</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">Description</TD><TD style="BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">Item Cost</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Total Cost</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">XXX</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">XXX</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-WEIGHT: bold">0</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">XXX</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ff00; FONT-WEIGHT: bold">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">£0.00</TD><TD style="BACKGROUND-COLOR: #ff0000"></TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">1 Pint Fresly Turned Caster</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ccff; FONT-WEIGHT: bold">XXX</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">20</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000">20</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E19</TD><TD>=VLOOKUP(B19,$M$18:$N$32,2)</TD></TR><TR><TD>F19</TD><TD>=A19*E19</TD></TR><TR><TD>P19</TD><TD>=IF(A19>O19,"None Available",O19-A19)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #0000ff; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #0000ff; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #0000ff; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #0000ff"><TBODY><TR><TD>Data Validation in Spreadsheet</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Allow</TD><TD>Datas</TD><TD>Input 1</TD><TD>Input 2</TD></TR><TR><TD>B19</TD><TD>List</TD><TD></TD><TD>=$M$18:$M$32</TD><TD></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Help summarizing inventory spreadsheet
Replies
3
Views
195
Skybluekid
Skybluekid
S
  • Question
Cell data from one sheet to another based on drop down box
Replies
3
Views
412
Alex Blakenburg
A
N
  • Question
Using formula to look up data where the data tab has the required data listed multiple times
Replies
1
Views
100
mumps
mumps
redspanna
  • Question
Merge 2 VB codes and add Application.screenupdating
Replies
2
Views
176
Bosquedeguate
Bosquedeguate
P
  • Solved
Copy lines of data based on a cell number, 1 = 10 copies
2 3
Replies
24
Views
371
Fluff
Fluff

Forum statistics

Threads
1,203,434
Messages
6,055,346
Members
444,781
Latest member
rishivar

Share this page

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
Back
Top