Dedicated Cell To Choose Lookup Table

  • Re: using a dedicated cell to choose lookup tables


    Try...


    =INDEX(C$2:C$15,SMALL(IF(A$2:A$15=E2,IF(B$2:B$15=F2,ROW(C$2:C$15)-ROW(C$2)+1)),SUM((E$2:E2=E2)*(F$2:F2=F2))))


    ...confirmed with CONTROL+SHIFT+ENTER.

  • Re: using a dedicated cell to choose lookup tables


    Forgot to mention...


    The tables in each of the lookup workbooks each have different ranges, so when a different lookup file is selected, the named range in the Main workbook needs to be dynamic and change accordingly to reflect the range of whichever lookup file that's selected.


    The lookup tables do not need to be dynamic since those ranges will not change, only the one in the Main wb, which functions as a global named range. I tried to make it dynamic by combining OFFSET with INDIRECT as in the following, but can't get it to work:


    Code
    1. =INDIRECT("'["&Main!$A$3&".xls]Sheet1'!$B$2:$U$3654"&COUNTA(Sheet1!$B:$U),1)


    Can you please let me know how to do this?


    Thanks

  • Re: using a dedicated cell to choose lookup tables


    Thanks Domenic.


    Your formula works, but you are using Fin Fang Foom's older (very slow) VBA code rather than his newer revised one below, which for some reason does not work. The code is pretty straightforward and not that different from the other one, so I don't see why it won't work. Very strange...


  • Re: using a dedicated cell to choose lookup tables


    Hmmm... Strange indeed! Unfortunately, as you know, I'm lost when it comes to VBA. Hopefully Fin or some other expert will be able to shed some light... :)

  • Re: using a dedicated cell to choose lookup tables


    Sorry Skye9 I only just signed up and saw that you've been using this forum recently.


    I know its a dumb question but i cant find anywhere to post a new thread?


    Can you help?


    Thanks,
    Joel

  • Re: using a dedicated cell to choose lookup tables


    Quote from Joel PJ

    ...i cant find anywhere to post a new thread?


    When you're in the first page of the forum, scroll down and click on 'New Thread'. Or click on 'Forum Tools' and select 'Post a New Thread'...


    Hope this helps!

  • Re: using a dedicated cell to choose lookup tables


    Joel,


    "New Thread" is a blue buttom with white font on the left side about half way down on the main page, just above the "Threads in Forum" section (its a busy page, look for blue globe, its just below that).