Can I identify variables using VLOOKUP, and then pass variable to another VLOOKUP?

Jeff C

New Member
Joined
Jul 3, 2012
Messages
5
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
If you are doing thing through VBA code then you'll want to use a different approach. I'd suggest using the .Find command to do something like this:
Code:
Sub test()
Dim EngCode As String
Dim DataFile As String
Dim lRow As Long
Dim SheetNumber As Integer
Dim RepCode As Integer
Dim Labor As Double
    'This gets the engine type from your quote spreadsheet that you want to look up.
    EngCode = Range("B6").Value
    
    MsgBox ("Select Supplier's workbook to open.")
    DataFile = Application.GetOpenFilename("Excel Files(*.xls; *.xlsx; *.xlsm), *.xls; *.xlsx; *.xlsm", 1, "Select One File To Open", , False)
    Workbooks.Open DataFile, UpdateLinks:=False
    
    'Below, "Main" is the name of the first sheet on your supplier's workbook.
    lRow = Worksheets("Main").Columns("A:A").Find(EngCode, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows).Row
    SheetNumber = Worksheets("Main").Range("C" & lRow).Value
    
    'For this section I use the variable RepCode to show how to find the labor for a certain value.
    'Use similar methods for find any other codes.
    RepCode = 10
    lRow = Worksheets("Sheet" & SheetNumber).Columns("A:A").Find(RepCode, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows).Row
    Labor = Worksheets("Sheet" & SheetNumber).Range("C" & lRow).Value
    
    ActiveWorkbook.Close False
End Sub

Obviously this code is simply pulling values into variables, so you'll have to add to the program so that it is actually doing something with these values.
I hope this gets you going in the right direction.
 
Upvote 0
Thank you very much. I'll plug this in and keep working, and I'll let you know how it works out.

You mentioned that this solution would work if I'm "doing [this] through VBA code." That got me thinking: is there a way to do what I'm attempting between the two workbooks without using VBA code?

I'm new to VBA and would love find a solution that's easy to understand and implement.
 
Upvote 0
I cross posted my original question to a Excel forums, and I was told that, for courtesy reasons, I should post links to those other posts so you all could see any efforts made by others.

Sorry if I created any confusion.

Excel Forums
OzGrid
 
Upvote 0
At first when I was thinking about this solution I was considering doing it with only formulas. You could make it work but it would involve some long, complicated, and confusing formulas that would end up being very easy to break if anything changes. VBA is probably the easiest solution and can be made to be more flexible and robust. Still, if for some reason you needed a solution not involving VBA we could probably make it work.
 
Upvote 0
Well, it turns out that I was indeed able to track down a solution to this issue that involved formulas. You're right that they are a little long, but I think VBA was a little too much for me to digest while trying to solve this problem.

Thank you very much for taking the time to respond to my post.

For posterity, the solution is captured at ExcelForums.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top