Hello All,
I have a vehicle repair excel sheet which I've put together over several months. vlookup works well, however with over a couple hundred items in the PartTbl list, I would never remember the number to Type for the lookup the service required.
In A21 I place the number 3, In cell B21 The formula =IF(A21="","",VLOOKUP(A21,PartTbl,2)) returns the value Air Conditioning.
In cell A22 I place the number 2, In cell B22 the formula =IF(A22="","",VLOOKUP(A22,PartTbl,2)) returns the value AirBag
In cell A23 I place the number 1. In cell B23 the formula =IF(A23="","",VLOOKUP(A23,PartTbl,2)) returns the value Labour.
Is there a way possibly using Data Validation or something to look in the column B that I could see the service required and the number appears in Column A
Here is a short clip of those cells.
Thanks in advance
Paul
I have a vehicle repair excel sheet which I've put together over several months. vlookup works well, however with over a couple hundred items in the PartTbl list, I would never remember the number to Type for the lookup the service required.
In A21 I place the number 3, In cell B21 The formula =IF(A21="","",VLOOKUP(A21,PartTbl,2)) returns the value Air Conditioning.
In cell A22 I place the number 2, In cell B22 the formula =IF(A22="","",VLOOKUP(A22,PartTbl,2)) returns the value AirBag
In cell A23 I place the number 1. In cell B23 the formula =IF(A23="","",VLOOKUP(A23,PartTbl,2)) returns the value Labour.
Is there a way possibly using Data Validation or something to look in the column B that I could see the service required and the number appears in Column A
Here is a short clip of those cells.
Excel 2003 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
20 | Task | PARTNAME | PART# | QTY | UNITPRICE | AMOUNT | ||||
21 | 3 | Air Conditioner | acunit | 1 | 109.00 | 109.00 | ||||
22 | 2 | Airbag | G50 - 15" | 1 | 25.00 | 25.00 | ||||
23 | 1 | Labour | Per Hour | 2 | 75.00 | 150.00 | ||||
AutoRepair |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G21 | =IF(A21="","",VLOOKUP(A21,PartTbl,4)) | |
G22 | =IF(A22="","",VLOOKUP(A22,PartTbl,4)) | |
G23 | =IF(A23="","",VLOOKUP(A23,PartTbl,4)) | |
H21 | =IF(F21="","",G21*F21) | |
H22 | =IF(F22="","",G22*F22) | |
H23 | =IF(F23="","",G23*F23) | |
B21 | =IF(A21="","",VLOOKUP(A21,PartTbl,2)) | |
B22 | =IF(A22="","",VLOOKUP(A22,PartTbl,2)) | |
B23 | =IF(A23="","",VLOOKUP(A23,PartTbl,2)) | |
E21 | =IF(A21="","",VLOOKUP(A21,PartTbl,3)) | |
E22 | =IF(A22="","",VLOOKUP(A22,PartTbl,3)) | |
E23 | =IF(A23="","",VLOOKUP(A23,PartTbl,3)) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
PartTbl | ='E:\Software Samples\Excel\automotive\Bill Saunders\[Bill Saunders Car Repair - Copy 20.xls]Services'!$A$4:$F$178 |
Excel 2003 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
3 | Task | PART NAME | PART # | Cost | MarkUp | Total | ||
4 | 1 | Labour | Per Hour | 75 | 11.25 | 86.25 | ||
5 | 2 | Airbag | G50 - 15" | 25 | 3.75 | 28.75 | ||
6 | 3 | Air Conditioner | acunit | 109 | 16.35 | 125.35 | ||
Services |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E4 | =D4*$G$4 | |
E5 | =D5*$G$4 | |
E6 | =D6*$G$4 | |
F4 | =D4+E4 | |
F5 | =D5+E5 | |
F6 | =D6+E6 |
Thanks in advance
Paul
Last edited: