Good morning,
I am looking for a tweak to a formula I have written in Excel 2010 please.
I am looking for the final balance for a given workorder. For example, for Work Order #5001, I want it to return 10, while for Work Order #6001, I want it to return 1.
I thought I had the nut cracked with the following formula, where R3 is a drop down menu with the Work Order values:
=IF(LOOKUP(R3,$F$2:$F$7901)=R3,VLOOKUP(R3,$F$2:$J$7901,5,1),"N/A")
It works like a charm if the data is consecutive, but if it can only handle on set of blank rows inbetween (why 1 set and not 2, I can't figure out)
The formula returns 10 for Work Order #5001, but returns 104 for Work Order #6001.
Any ideas?
Thanks!
<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2><COL></COLGROUP>
I am looking for a tweak to a formula I have written in Excel 2010 please.
I am looking for the final balance for a given workorder. For example, for Work Order #5001, I want it to return 10, while for Work Order #6001, I want it to return 1.
I thought I had the nut cracked with the following formula, where R3 is a drop down menu with the Work Order values:
=IF(LOOKUP(R3,$F$2:$F$7901)=R3,VLOOKUP(R3,$F$2:$J$7901,5,1),"N/A")
It works like a charm if the data is consecutive, but if it can only handle on set of blank rows inbetween (why 1 set and not 2, I can't figure out)
The formula returns 10 for Work Order #5001, but returns 104 for Work Order #6001.
Any ideas?
Thanks!
| Date</SPAN> | Shift</SPAN> | Work Order #</SPAN> | Item #</SPAN> | Physical Cases Planned</SPAN> | Physical Cases Produced</SPAN> | Balance</SPAN> |
| 6/2/2012</SPAN> | 3rd Shift</SPAN> | 5001</SPAN> | 410164</SPAN> | 1101</SPAN> | 50</SPAN> | 51</SPAN> |
| 6/2/2012</SPAN> | 3rd Shift</SPAN> | 5001</SPAN> | 410164</SPAN> | 1101</SPAN> | 50</SPAN> | 10</SPAN> |
| 6/2/2012</SPAN> | 3rd Shift</SPAN> | |||||
| 6/2/2012</SPAN> | 3rd Shift</SPAN> | |||||
| 6/2/2012</SPAN> | 3rd Shift</SPAN> | |||||
| 6/2/2012</SPAN> | 1st Shift</SPAN> | 6001</SPAN> | 433748</SPAN> | 1104</SPAN> | 500</SPAN> | 604</SPAN> |
| 6/2/2012</SPAN> | 1st Shift</SPAN> | |||||
| 6/2/2012</SPAN> | 1st Shift</SPAN> | |||||
| 6/2/2012</SPAN> | 1st Shift</SPAN> | |||||
| 6/2/2012</SPAN> | 1st Shift</SPAN> | |||||
| 6/2/2012</SPAN> | 2nd Shift</SPAN> | 6001</SPAN> | 433748</SPAN> | 1104</SPAN> | 500</SPAN> | 104</SPAN> |
| 6/2/2012</SPAN> | 2nd Shift</SPAN> | |||||
| 6/2/2012</SPAN> | 2nd Shift</SPAN> | |||||
| 6/2/2012</SPAN> | 2nd Shift</SPAN> | |||||
| 6/2/2012</SPAN> | 2nd Shift</SPAN> | |||||
| 6/2/2012</SPAN> | EOD Shift</SPAN> | 6001</SPAN> | 433748</SPAN> | 1104</SPAN> | 100</SPAN> | 1</SPAN> |
<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2><COL></COLGROUP>
