Lookup tables refering to unopen worksheet

    Good Morning,


    I am trying to create an order form using lookup tables. With Customer information coming from one worksheet and "skus",pricing, qnty blah, blah..coming from another worksheet. Is there a way I can access these other worksheets without actually having them open?

    I am not a VBA person and have tried to do this with formulas, here's what the formula looks like:


    =LOOKUP($A14,'C:\Documents and Settings\Mark B\My Documents\[sku2.xls]Sheet1'!$A$2:$A$28000,'C:\Documents and Settings\Mark B\My Documents\[sku2.xls]Sheet1'!D$2:D$28000)


    I have also tried to attach a copy of the order form.


    Any suggestions would be most helpful.


    Thanks
    Mark

    VLookup shopuld work whether the referenced workbook is open or not. However your syntax is wrong. For the example above you should have
    =LOOKUP($A14,'C:\Documents and Settings\Mark B\My Documents\[sku2.xls]Sheet1'!$A$2:$D$28000,4)
    See help for how the arguments in VLookup work. It also assumes the data in column A is in ascending order. If it is not and you are looking for an exact match, then add a final input argument of FALSE to Vlookup (after the 4 above).

    Can I search exact match without sorting is first?


    Can I also get a "return" of col. b, col. d, col L but not "C,e, f, g, h, i," etc?


    Thanks

    No need to sort when you are looking for an exact match (but you will need that FALSE in the argument list of VLOOKUP). If you want other columns returned just change the 4 to the appropriate column in the table reference you pass and make sure the table reference includes the column. Ii you want a lot of column returns, it is more efficient to use MATCH in an empty column to find the appropriate line that you are looking for and then INDEX in repeated columns to return the values you want. See help for how to use the functions.

    So...I entered the formula as:
    =LOOKUP($A14,'C:\Documents and Settings\Mark B\My Documents\[SKU2.XLS]Sheet1'!$A2:$A$28000,4)

    and am getting


    "Excel cannot complete this task with available resources. Choose less data or close other applications".


    Why?


    I tried the Match and got the appropriate line back, but received N/A# when INDEX.


    Is MATCH/INDEX the best way? I have SKU2 at 28000 lines and 6 columns.


    thanks

    The formula should be=LOOKUP($A14,'C:\Documents and Settings\Mark B\My Documents\[SKU2.XLS]Sheet1'!$A2:$D$28000,4)
    with a D in the last column reference. That may not be what's causing the error, but it's worth a try.
    Index should work on a closed workbook (I just tried it).
    I would suggest opening the other workbook and make sure the formulas work then. Once the syntax and results are what you want, then close the other workbook and try it.

    The error has to do with the amount of lines.


    =LOOKUP($A14,'C:\Documents and Settings\Mark B\My Documents\[sku2.xls]Sheet1'!$A2:$D$16000,4)


    When I "back down" $D$28000, the error is no longer there. So it appears I have hit a lookup limit? Does Match/Index have limits?


    How do I get around this one?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login