Dedicated Cell To Choose Lookup Table

Fin Fang Foom · Mar 12th 2006

Re: using a dedicated cell to choose lookup tables

Thank You for responding. What I'm trying to do is that the formula does not have to be Large or Small it will pick up the first one it see's.

**Domenic** · Mar 12th 2006

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.

Fin Fang Foom · Mar 12th 2006

Re: using a dedicated cell to choose lookup tables

Once again Domenic you are the best I've seen.
Thank You!

skye9 · Mar 21st 2006

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

=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

**Domenic** · Mar 21st 2006

Re: using a dedicated cell to choose lookup tables

Try...

=OFFSET(INDIRECT("'["&Main!$A$2&".xls]Sheet1'!B2"),,,COUNTA(INDIRECT("'["&Main!$A$2&".xls]Sheet1'!B2:B65536")))

Hope this helps!

skye9 · Mar 21st 2006

Re: using a dedicated cell to choose lookup tables

Thanks Domenic,

but I couldn't get your formula to work either. Shouldn't the first "B2" be a range?

**Domenic** · Mar 21st 2006

Re: using a dedicated cell to choose lookup tables

Have a look at the attached files. Does this help?

skye9 · Mar 22nd 2006

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...

Code

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Intersect(Target, Range("C2")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub 
    ActiveSheet.EnableCalculation = True 
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationAutomatic 
     
    With Application.FileSearch 
        .NewSearch 
        .LookIn = "C:\" 
        .Filename = [A2] & ".XLS" 
         
        If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then 
            Workbooks.OpenText .FoundFiles(1), xlWindows 
            ActiveWorkbook.Close 
             
        End If 
    End With 
     
    ActiveSheet.EnableCalculation = False 
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
     
End Sub

Display More

**Domenic** · Mar 22nd 2006

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...

Fin Fang Foom · Mar 22nd 2006

Re: using a dedicated cell to choose lookup tables

I down loaded Domenic recent sample file and copy the code you just provided and it works fine.

skye9 · Mar 22nd 2006

Re: using a dedicated cell to choose lookup tables

yes got it working now.

Thanks

Joel PJ · Mar 22nd 2006

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

**Domenic** · Mar 22nd 2006

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!

Joel PJ · Mar 22nd 2006

Re: using a dedicated cell to choose lookup tables

Thanks for replying Domenic

I headed to the first page in the forum and there is no link to a "new thread" or anything of the sort.

The main page i went to is http://www.ozgrid.com/forum

Am i in a sub heading???

Thanks,
Joel

skye9 · Mar 22nd 2006

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).

Joel PJ · Mar 22nd 2006

Re: using a dedicated cell to choose lookup tables

Thanks so much Skye

I found it! I was on the wrong page to begin with. Your instructions were very helpful. Thanks for replying

Joel

Dedicated Cell To Choose Lookup Table

Participate now!

Share

Users Viewing This Thread