multiple vlookups over various sheets

[brunette]

Morning,

Using Excel 2007 I currently have the following sheets:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>F</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Cepro Code</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">Description</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">In Stock</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">Min stock</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">Estimated Order</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">Order</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">Received</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">200710015</td><td style="border-top: 1px solid black;border-right: 1px solid black;;">HP LJ5200 Toner Black Q7516A</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">5</td><td style="text-align: right;border-top: 1px solid black;background-color: #F2F2F2;;">0</td><td style="text-align: right;border-top: 1px solid black;background-color: #F2F2F2;;">0</td><td style="text-align: right;border-top: 1px solid black;background-color: #F2F2F2;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #F2F2F2;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-left: 1px solid black;;">200710105</td><td style="border-right: 1px solid black;;">HP LJM3035xs Toner - Black Q7551X</td><td style="text-align: right;border-left: 1px solid black;background-color: #F2F2F2;;">32</td><td style="text-align: right;background-color: #F2F2F2;;">0</td><td style="text-align: right;background-color: #F2F2F2;;">0</td><td style="text-align: right;background-color: #F2F2F2;;"></td><td style="text-align: right;background-color: #F2F2F2;;">#N/A</td></tr></tbody></table><p style="width:6,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Consumables</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=G3-M3+L3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J3</th><td style="text-align:left">=IF(<font color="Blue">H3<=I3,1,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L3</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">VLOOKUP(<font color="Green">A3,'Orders Received'!C:D,6,FALSE</font>)</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
In L4 I have this formula: =VLOOKUP(Consumables!A4;'Orders Received'!A:D;6;FALSE)
The formula in J3 is working perfectly.
The formulas in L3 and 4 are currently NOT working.

Sheet 2 is as follows:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Delivery Date</td><td style="font-weight: bold;text-align: center;;">Doc. No.</td><td style="font-weight: bold;text-align: center;;">Description</td><td style="font-weight: bold;text-align: center;;">Cepro Code</td><td style="font-weight: bold;text-align: center;;">Ordered</td><td style="font-weight: bold;text-align: center;;">Received</td><td style="font-weight: bold;text-align: center;;">Outstanding</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">03-Jun</td><td style=";">T11-/1642</td><td style=";">HP LJM3035xs Toner - Black Q7551X</td><td style="text-align: right;;">200710105</td><td style="text-align: right;;">20</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">04-Jun</td><td style=";">T11-/1643</td><td style=";">Epson C4200 Photoconductor C13S051109</td><td style="text-align: right;;">200710125</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:9em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Orders Received</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">D2,Consumables!A:F,6,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">D3,Consumables!A:F,6,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=+E2-F2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=+E3-F3</td></tr></tbody></table></td></tr></table><br />

All the formulas on sheet 2 are working perfectly.

What I need to do is to get the "in stock" values in Column H sheet Consumables, to update, based on changes made to "received" in sheet Orders Received.

At the moment the framework is there but I'm having trouble getting the vlookup formula in L4 sheet Consumables to work correctly and I'm not sure why it's not working. As you can see, it should return a value of 20 and it's giving me #N/A. Can't figure out why.

The formula in L3 is basically the same formula but I want it to ignore any possible #N/A errors and return a 0 instead, so that addition/subtraction instructions will still work.

Your help in getting the formula in L4 to work is greatly appreciated. I will need to use the same formula in other cells as I build the rest of the database. I'm still on the orders received part of it. Stock dispatched is up next!

Thanks again,
Bru

 

[moonfish]

The vlookup in your first sheet is trying to find the sixth column of the specified range 'Orders Received'!C:D.

Because this range contains only two columns, C and D, it will not work.

 

[brunette]

The way I understand vlookup formulas to work is that it will look for the value in A2 (which is there, just hidden). It should look for that value anywhere in columns A-D in sheet 2 and once it finds a match, return whatever value is in the 6th column of the corresponding row. That's what I want it to do, anyway.

This is working fine in sheet 2 but not in sheet 1 and that's my problem. I want it to work in sheet 1 also. It's almost as if it's telling me that it's not finding a corresponding value, when it is there.

Also, the formula you're referring to is incorrect. I am aware of this. However, I must first get the formula in the cell below it to work properly, before I can fix that one.

Thanks again

 

[brunette]

Just to let you know I found a workaround this problem, that doesn't involve vlookup at all and, I think, worked out to be much simpler anyway. Used sumif instead and got it all working beautifully. The reason for this was that I realised that, as orders came in, I would have repeated instances of each code, making vlookup impossible anyway. So yeah, SUMIF to the rescue.

I'm very grateful for the forum and for the search function and for all your wonderful ideas/solutions. Thank you!

 

[moonfish]

I'm glad to hear that you got it to work.
For future reference though, the vlookup function does not work as you described.

vlookup(target,range,column_no,true/false)
Vlookup tries to find the target value in the first column of the specified range. When found it will return to you the value, in target's row and in the column specified by column_no, from the specified range.

Example:

Excel Workbook
	A	B	C	D	E	F
1	*	*	*	col1	col2	col3
2	*	*	1	a	b	c
3	*	*	4	f	e	d
4	*	*	8	g	h	i
5	*	*	3	l	k	j
6	*	*	*	*	*	*
7	*	*	*	h	*	*
Sheet

The formula in the yellow cell returns the value from column E, because E is the third column in the range C2:F5.