+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    07-03-2012
    Location
    Neuquen, Argentina
    MS-Off Ver
    Excel 2010
    Posts
    10

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

    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.
    Last edited by Jeff C; 07-05-2012 at 05:34 PM. Reason: issue solved

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    Hi

    From your description I think it could be done, but how about an example workbook so we have something concrete to work with....

    rylo

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

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

    Cross posted: Ozgrid
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  4. #4
    Registered User
    Join Date
    07-03-2012
    Location
    Neuquen, Argentina
    MS-Off Ver
    Excel 2010
    Posts
    10

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

    Sure, and thank you for taking the time to look at my issue.
    my_price_quote.xls
    Supplier.xls
    These two files are slimmed down versions of my supplier's list and my price quote workbook. Most of the language is in Spanish, but the column heads are in English and should suffice for understanding.
    Thanks again!

  5. #5
    Registered User
    Join Date
    07-03-2012
    Location
    Neuquen, Argentina
    MS-Off Ver
    Excel 2010
    Posts
    10

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

    I cross posted my original question to a couple different 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.

    OzGrid
    MrExcel

  6. #6
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    Hi

    Just to test things out, I've opened both files in Excel. The supplier file opened for me as Supplier(1).xls - just to explain the formulas.

    In my_price_quote, I put BED200 in B6.
    C1: ="'[Supplier(1).xls]" & VLOOKUP(B6,'[Supplier(1).xls]MAIN'!$A$2:$C$14,3,FALSE) & "'!B:C"
    F10: =VLOOKUP(C10,INDIRECT($C$1),2,FALSE)
    Copied from F10 to F39.

    I don't know if the price should be in F or G (I can't speak Spanish), but you should get my drift.

    INDIRECT will only work when the supplier file is open. But is this a viable solution?

    rylo

  7. #7
    Registered User
    Join Date
    07-03-2012
    Location
    Neuquen, Argentina
    MS-Off Ver
    Excel 2010
    Posts
    10

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

    Thank you RYLO.

    I tried your suggestions, but unfortunately Excel did not accept the formula in cell C1. It does not like the reference to....B6,'[Supplier....in the first VLOOKUP. It highlights that string and also shows B6 in blue.
    • In the my_price_quote.xls spreadsheet I attached earlier, cell B6 is actually a merge of three cells (for aesthetic reasons). No matter where I click in those three merged cells, Excel always shows B6 as its name, so I don't think the fact that it's a merged cell is a problem. (Or is it?)
    • B6 is actually a drop down list that pulls values from another file. (I defined a Name that points to =[Supplier.xls]MAIN!$B2:$B14.) Do you think this could be the issue?
    • Might this issue be caused by the single- and double-quotes? They don't appear to open/close well to my eye, but I'm new at this and could very well be wrong.

    Thank you again for helping me.

  8. #8
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    Hi

    I couldn't make your dropdown list work, which is why I just put a value directly into B6. Maybe select that cell directly and put in the BED200 value.

    Did you update the formula for the correct name for supplier? As I said, when I opened it, it came back with the (1) in the name. This time I saved the file, and then put the formula ="'[Supplier.xls]" & VLOOKUP(B6,[Supplier.xls]MAIN!$A$2:$C$14,3,FALSE) & "'!B:C" into C1, and it came back with the result of '[Supplier.xls]4'!B:C which is what it should show. Of course, supplier has to be open in the same instance of Excel as my_price_quote for INDIRECT to work. If that still doesn't work, the select say A1 in pricequote, and link the cell directly to the supplier file, and see what the structure of the resulting formula is. It may be that there is a different syntactical structure for the version of Excel you are using, so if there is a difference, then update the formula in C1 accordingly.

    rylo

  9. #9
    Registered User
    Join Date
    07-03-2012
    Location
    Neuquen, Argentina
    MS-Off Ver
    Excel 2010
    Posts
    10

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

    Hi,

    Yes, I updated the formula you posted to reflect the correct name of the supplier workbook. And I have both files open within the same Excel instance. Unfortunately, I still get an error.

    It's strange, but Excel will not let me go past the lookup_value argument in the VLOOKUP formula.

    When I begin typing my formula ...... =vlookup(B6, ...... (or if I paste in yours) Excel tells me there's an error with that lookup_value (B6) and it won't let me proceed. It doesn't matter if B6 contains a drop down list or text I typed directly. And I've also played with formatting the cell as Text or General, all to no avail.

  10. #10
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    HI

    What happens if you try to do a vlookup formula in a completely new workbook? Does it work? If so, then can you do a vlookup in the price workbook that doesn't refer to B6? Does it work?

    I don't have 2010, so is there anything special about Vlookup in that version?

    rylo

  11. #11
    Registered User
    Join Date
    07-03-2012
    Location
    Neuquen, Argentina
    MS-Off Ver
    Excel 2010
    Posts
    10

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

    Hi Rylo,

    Problem solved!

    I started from scratch in a new workbook with a simple example. While using Excel's formula wizard, I noticed that Excel 2010 was separating the arguments in my formula with semicolons and not with commas. That's all it was -- semicolons instead of commas.

    I returned to my price quote workbook and plugged in your formulas. After a bit of troubleshooting I was able to make it all work. For the benefit of others who might land on this page, here are the two tweaked formulas that ended up working:

    Please Login or Register  to view this content.
    This formula sits in a cell (I6) off to the side of my price quote form, so it won't be visible when I print out a price quote for my customers. The VLOOKUP portion of the formula takes the name of the engine captured in cell C6 and searches for it on the ENGINES worksheet of the Supplier.xls workbook. Once found, it retrieves the name (expressed as a number) of another worksheet in the same Supplier.xls workbook that contains labor charges for rebuilding that engine. Bookended around the VLOOKUP is text that will be combined with the VLOOKUP's results to create another reference to the Supplier.xls document that is then used in the formula below. Example result: '[FACRA.xls]6'!$B$7:$C$500 where 6 is the name of the worksheet returned by the VLOOKUP.

    Please Login or Register  to view this content.
    This formula appears in each "Labor Unit Price" field on my price quote form. It's another VLOOKUP that uses the INDIRECT function to take the results of the first formula from cell I6 and go back to the Supplier.xls workbook and retrieve the labor price.

    Thanks very much, Rylo, for suggesting that I start with a clean workbook. That helped a lot. Fresh eyes, and all that.

    Cheers.

  12. #12
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    Hi

    isn't it the way, that sometimes the simpliest things just trip you up. There is possibly (probably) a setting somewhere that will allow you to change the ; to a , as the separation variable, but for the life of me, I don't know where to start looking.

    rylo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1