Dedicated Cell To Choose Lookup Table

    I was wondering if it's possible to manually choose a specific lookup table from another workbook by typing the table name into a dedicated cell?


    I'd like to be able to type the table name into cell A1 (or use a drop-down menu) and choose a specific table to use as the data reference for another group of cells.


    For example, typing in, or selecting from drop-down "Costs2005" (or "Costs2004", etc.) selects that table to be used for cells B2:D10.


    Thanks for any insights :)

    Re: using a dedicated cell to choose lookup tables


    Do you mean something like :


    =VLOOKUP(E5,INDIRECT("table1book.xls!"&D5),2,FALSE)


    attached. Both books have to be open for INDIRECT to work.


    HTH

    Re: using a dedicated cell to choose lookup tables


    thanks Carl.


    That was pretty simple. But I can't seem to get it to work. I created another similar table and called it table2, and when I type "table2" in cell D5, it returns #N/A (how do you eliminate #N/A from displaying?). Perhaps I'm missing something. Also, I'd prefer not to have to open the table workbook?

    Re: using a dedicated cell to choose lookup tables


    Hi,


    This example uses IF and ISERROR to trap the error codes and keep them from displaying. ie IF ISERROR = true display "" (blank).


    I've also changed the INDIRECT to call from the active sheet (this sheet), a second sheet (that sheet), and an xlveryhidden sheet (the other sheet).



    Cheers,


    dr

    Re: using a dedicated cell to choose lookup tables


    thanks a lot dr!


    Works great. But I'm confused as to why you included the other two sheets since it seems to work fine with only with the "This Sheet".


    Also, can the formula be modified to perform a 2 column (condition) lookup, where one of the conditions is an exact match and the other is not, but rather the closest match? For instance, to look up Dates in column 1, and Times column 2 of a table, where the Dates must match exact, but the Times will not, and so to find the closest (or next) Time value, and then return the value of column 3.


    Like this:


    INPUT DATA
    A | B
    10/3/05 | 2:58 PM



    LOOKUP TABLE
    A | B | C
    1/24/04 | 10:30 AM | 489
    10/3/05 | 9:17 AM | 736
    10/3/05 | 2:42 PM | 265
    10/3/05 | 3:05 PM | 914


    3 exact Date matches, but 3:05 PM is closest to 2:58 PM, and thus returns "914".



    Thanks for helping out :)

    Re: using a dedicated cell to choose lookup tables



    Look at attached file to see 2 possible solutions.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: using a dedicated cell to choose lookup tables


    I attach the sample files below. Save these files directly in your C:\Drive.


    Now in lookupbook.xls in cell D5 there is a Dropdown box select what workbook you whant to use and you notice the letters will change. This is just a sample let me know if this is ok?

    Re: using a dedicated cell to choose lookup tables


    Thank you so much Wigi and Fin Fang Foom!


    Both formulas work great, with a couple exceptions.


    Wigi, the formula accepts non-matching (non-existing) years. But the years must match, otherwise no data is to be return, and the cell remains empty.


    FFF, your code works perfectly for the existing tables, but I could not get it to work after changing the folder path in cell I5 to a different file & named range (on a different drive). I am obviously overlooking something. Also, I may want to have cell D5 reference another cell that contains the file name (lookup table) so that the selection is automatic. Can you please tell me where in the formula would I insert the cell reference to allow for this?


    Then, how would I integrated the two formulas? That is, to be able to select the lookout table (based on a file name) and have the lookup meet the 2 conditions - exact Year match, and closest Time match?


    Thanks again to both of you for your help :)
    S.

    Re: using a dedicated cell to choose lookup tables


    I think it probably be better if you provide a sample file with the formulas you would like to use and the folder path so I could see how your data is arrange.

    Re: using a dedicated cell to choose lookup tables


    Quote from skye9

    Wigi, the formula accepts non-matching (non-existing) years. But the years must match, otherwise no data is to be return, and the cell remains empty.


    Where can I find an example of you'd like to have the non-existing years issue? Providing that formula won't be difficult, but I don't know where to look for it in your sheets above. I suppose that


    Code
    =IF(year = non-existing,"",earlier formula)


    would suffice.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Re: using a dedicated cell to choose lookup tables


    Thanks Wigi and FFF.


    I've attached a sample of what I'm trying to do.


    The 'DatesTimes' file contains the Filename (in A2) for the particular lookup table to use, and the Dates & Times (in B & C) to look up. Any dates with years not part of that Lookup file (ie. 2005) are igored and the $Amt column is left blank. (filepath for the lookup table is E:\Docs\2005\)


    I will be pasting in the data into the DatesTimes sheet but I would also like the option of choosing a particular lookup file (in A2) from a drop-down menu (perhaps by having the list of fileames or lookup table names listed in a separate column and referencing that list through A2).


    Pretty simple but please let me know if you need more clarification.


    Thanks again for your help.

    Re: using a dedicated cell to choose lookup tables


    Wigi, Fin Fang Foom (or anyone) - can you please let me know how to integrtate (and simplify) your formulas and finalize this task?


    To reiterate:
    to use cell A2 to point to a specific lookup table and perfrom a 2 condition lookup - where only 1 condition (Date) is an exact match, and the other condition (Time) is the closest match.


    Thanks, you help is very much appreciated.

    Re: using a dedicated cell to choose lookup tables


    Assuming that A1:C4 contains your data, E1 contains the first condition, such as a date, and F1 contains the second condition, such as a time, try...


    =INDEX(C1:C4,MATCH(E1,A1:A4,0)+MATCH(MIN(ABS(INDEX(B1:B4,MATCH(E1,A1:A4,0)):INDEX(B1:B4,MATCH(E1,A1:A4))-F1)),ABS(INDEX(B1:B4,MATCH(E1,A1:A4,0)):INDEX(B1:B4,MATCH(E1,A1:A4))-F1),0)-1)


    ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.


    See the attached file...


    Hope this helps!

    Re: using a dedicated cell to choose lookup tables


    I forgot to mention that my solution is based on the assumption that A1:C4 is sorted by Column A, in ascending order. Is this the case?

    Re: using a dedicated cell to choose lookup tables


    Thanks Domenic.


    your formula works nicely for the 2 conditions (exact match and closest match) requirement. But it does not include the second requirement which is the ability to select a specific lookup table by typing that filename into a cell and being able to select a specific lookup table. Also, #N/A cells are to remain empty.


    (see example files I attached in a recent post)


    Thanks for your help. :)

    Re: using a dedicated cell to choose lookup tables


    INDIRECT can be used to choose the file of interest. The problem, though, is that all files would need to be opened. Are you still interested?


    Personally, I think Fin's solution would be better, especially if it allows files to be closed. My formula could be used in conjunction with his code. Unfortunately, I'm not familiar with VBA so I can't help you there.

    Re: using a dedicated cell to choose lookup tables




    Hi Domenic,


    skye9 would like to have a multiple lookup formula that returns multiple results but the closes match. I've been trying to use the formula you and Krishnakumar provided to me not too long ago but it seems i can't get it to work. skye9 posted his sample workbook in one of his recent post. The sample file he posted is named DatesTimesList.xls


    =IF(ROWS($1:2)+1-MATCH(9.9E+307,$C$1:C2)<=COUNTIF($A$1:$A$7000,LOOKUP(9.9E+307,$C$1:C2)),INDEX($B$1:$B$7000,SMALL(IF($A$1:$A$7000=LOOKUP(9.9E+307,$C$1:C2),ROW($B$1:$B$7000)),ROWS($1:2)+1-MATCH(9.9E+307,$C$1:C2))),"")

    Re: using a dedicated cell to choose lookup tables


    Quote from Fin Fang Foom

    Hi Domenic,


    skye9 would like to have a multiple lookup formula that returns multiple results but the closes match. I've been trying to use the formula you and Krishnakumar provided to me not too long ago...


    Hi Fin!


    I don't think that formula will return the OP's desired results... :)

    Re: using a dedicated cell to choose lookup tables


    Skye9,


    After taking another look, I've discovered that the formula I offered is flawed. For example, if 1/24/04 is entered as the date, and 10:00 AM is entered as the time, it returns #N/A, which is incorrect.


    Nevertheless, here's how I would do it. I would have all relevant tables on one sheet and include a column designating the year. Then the formula would be relatively simple. Have a look at the attached file and see if it will do...

    Re: using a dedicated cell to choose lookup tables


    Thanks Domenic and FFF.


    Domenic, the lookup tables must be kept in separate files, and preferably closed. BTW, I should have considered the use of a time range limiter so that if the time requested is not within a certain reasonable range, then the cell remains empty (but that's ok).


    Forgive me if I’m oversimplifying the matter, but I thought you could just insert the table-referencing cell (ie ‘A2’ or whatever cell) into the formula as the reference pointer to the lookup table(s), and then accessing a particular file/table by typing its name into that cell by concatenating part of the file path & extension in the formula. (I think that's what Fin was doing with 'SUBSTITUTE').


    Or, by creating an auxiliary table in a separate column in the main worksheet that lists of the names of the lookup files or named ranges for the project. Then, typing the name of that file or table into the cell references that name in the auxiliary table, which then points to the corresponding lookup table.


    I didn’t think it would be so difficult to reference separate tables in separate files using data input into a cell. But I think you guys are close to getting it.


    Thanks again. :)

Participate now!

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

Register Yourself Login