LinkBack URL
About LinkBacks
I'm trying to set up an Excel (2010) spreadsheet to generate price quotes. I want to pull pricing information from a separate Excel workbook that I get directly from my supplier. I must emphasize that I cannot change the format of my supplier's list -- I must work with it as it was given to me.
The first sheet of my supplier’s workbook is a list of hundreds of engines. Each record has a corresponding number pointing to one of 13 other worksheets that contain labor charges for repairing these engines. Since there are many more engines than labor worksheets, there’s a many-to-few relationship at work here where dozens of different engine records could point to the same labor charge worksheet. Here’s a brief example:
CODE..........ENGINE..................................................SEE LABOR SHEET
BED200.....BEDFORD 200 DIESEL *4CIL* 98.4mm.........................4
BED300.....BEDFORD 300 DIESEL *4CIL* 98.4mm.........................4
BED350.....BEDFORD 350 DIESEL *4CIL* 106.3mm........................6
Here’s a snapshot of one of those 13 worksheets in the same file that contain labor charges:
CODE..........REPAIR..................................................LABOR CHARGE
10...............Rebuild cylinders....................................205.61
20...............Change Piston Injectors...........................74.24
30...............Re-machine camshaft throats.....................411.23
33...............Microscan of crankshaft surfaces...............258.21
Near the top (cell B6) of my separate price quote spreadsheet I have a drop down list for selecting an engine. (This drop down list functions fine – I don’t need help with it.) Once I select an engine, however, I want Excel to identify the corresponding worksheet containing labor charges and then automatically go get that information and bring it into my price quote.
My thinking was to capture the page number for the labor sheet as a variable, and then use that variable to tell Excel what worksheet to go to in order to get the prices. The VBA code below identifies the variable SheetNumber and attempts to identify it using a =VLOOKUP command. (Can I do that?) I’m trying to take the engine selected in cell B6 of my price quote and then go to the Motors sheet of file Master.xls and find the page number (in col 3) that corresponds with that engine. I want this variable to end up being an integer (1-13) representing the worksheet where the labor charges for the selected motor reside.
DIM SheetNumber as Integer
SheetNumber = (=VLOOKUP(B6,MASTER.XLS[MOTORS]!$A$1:$C$500,3,FALSE))
Below that I have a long list of almost identical lines of code that tell Excel to go to the worksheet (held in variable SheetNumber) and retrieve prices for each labor category. I want Excel to bring only the price information back to my price quote form. Here’s one of those (200+) lines of code.
Range ("G10").Value = (=VLOOKUP(C10,FACRA.XLS[SheetNumber]!$A$6:$C$500,3,FALSE))
My approach simply doesn’t work. Excel chokes at the first VLOOKUP command where I’m identifying my variable.
1. How can I identify a worksheet number based on the engine the user selects in cell B6 and then save that number as a variable for later use?
2. How can I then use that variable’s value to tell Excel to go to a specific worksheet in a separate file and retrieve corresponding pricing information?
Sorry for the long question. Any help is appreciated.
Register To Reply
Bookmarks