Hi all
I can't even begin to determine how to approach what I would like to happen. Can I use a formula or should I use code?
I have an Inv # in col "A" and a Description in col "I".
I also have a table ("wordpercent") on a sheet "Info" with a list of words - Col "N" and a corresponding % - Col "O".
I want to place a formula in col "J" to return a corresponding % if any word in the Description col - Col "I" is listed in the table, and if it can't find a word in the list to then look at the Inv # - Col "A" and if it begins with a "1" or "6" then return the corresponding % - Col "O"
Below is a sampling of the data on "Sheet2". The actually data is hundreds of rows long.
Any help or ideas, even if it's a negative "no can do" is greatly appreciated.
Thank you
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>
I can't even begin to determine how to approach what I would like to happen. Can I use a formula or should I use code?
I have an Inv # in col "A" and a Description in col "I".
I also have a table ("wordpercent") on a sheet "Info" with a list of words - Col "N" and a corresponding % - Col "O".
I want to place a formula in col "J" to return a corresponding % if any word in the Description col - Col "I" is listed in the table, and if it can't find a word in the list to then look at the Inv # - Col "A" and if it begins with a "1" or "6" then return the corresponding % - Col "O"
Below is a sampling of the data on "Sheet2". The actually data is hundreds of rows long.
Any help or ideas, even if it's a negative "no can do" is greatly appreciated.
Thank you
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
1 | Inv # | Customer | Order Qty | Unit Price | Ext Price | Status | Date | PO | Description | % | Profit | Oil | 1% | ||
2 | 15759-1 | a | 1 | 15.00 | $15.00 | Pending | #### | Warranty Work | 0 | Warranty | 4% | ||||
3 | 16420-1 | b | 1 | 28.00 | $28.00 | Complete | #### | Warranty Leak Repair | 0 | inbound | 4% | ||||
4 | 16426-1 | b | 1 | 22.00 | $22.00 | Complete | #### | T&M Repair Work | 0 | outbound | 4% | ||||
5 | 16521-1 | b | 1 | 29.00 | $29.00 | Complete | #### | removal and shipment | 0 | parts | 17% | ||||
6 | 16571-1 | a | 1 | 10.00 | $10.00 | Complete | #### | CEDA Warranty Work | 0 | CEDA | 2% | ||||
7 | 16616D-O | a | 114 | 2.70 | $307.80 | Active | #### | Type II Oil delivery | 0 | FWI | 4% | ||||
8 | 16616E-O | a | 113 | 2.70 | $305.10 | Active | #### | Type II Oil Delivery | 0 | FWO | 4% | ||||
9 | 16616F-O | a | 112 | 2.70 | $302.40 | Active | #### | Type II Oil Delivery | 0 | iInv # begins with 1 | 4% | ||||
10 | 16616G-O | a | 111 | 2.70 | $299.70 | Active | #### | Type II Oil Delivery | 0 | Inv # begins with 6 | 2% | ||||
11 | 16624A-2 | b | 1 | 19.00 | $19.00 | Complete | #### | Assist in repair work | 0 | ||||||
12 | 16662-1 | e | 1 | 26.00 | $26.00 | Pending | #### | 0 | |||||||
13 | 167 | o | 1 | 0.00 | $0.00 | Hold | #### | 0 | |||||||
14 | 16705 | s | 1 | 5.00 | $5.00 | Complete | #### | Counter | 0 | ||||||
15 | 16712-1 | p | 1 | 19.50 | $19.50 | Pending | #### | Warranty Work | 0 | ||||||
16 | 61551 | x | 1 | 36.00 | $3,666.20 | Complete | #### | Various parts | 0 | ||||||
17 | 61552 | f | 1 | 47.00 | ######## | Active | #### | 10 MRE Repair{DC} | 0 | ||||||
18 | 61552FWI | f | 1 | 2.60 | $2,690.00 | Complete | #### | 2 MRE Inbound Frt{DC} | 0 | ||||||
19 | 61552FWO | f | 1 | 2.60 | $2,690.00 | Hold | #### | 2 MRE Outbound Frt{DC} | 0 |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>