Dedicated Cell To Choose Lookup Table

Re: using a dedicated cell to choose lookup tables

Hi Domenic,

Disregard my last post it did not make any sense.

Here is the sample workbook I would like you to take a look.

Re: using a dedicated cell to choose lookup tables

Try...

G2, copied down:

=INDEX($C$2:$C$7,LARGE(IF($B$2:$B$7=F2,ROW($B$2:$B$7)-ROW($B$2)+1),COUNTIF($F$2:F2,F2)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Re: using a dedicated cell to choose lookup tables

Thank You Domenic it does work But i forgot to mention that in column C is always going to be out of order it never going to be ascending order or decesending order. Can the formula looks for the largest number. Is that possilbe? Sample workbook below.

Re: using a dedicated cell to choose lookup tables

In that case, try...

=LARGE(IF($B$2:$B$7=F2,$C$2:$C$7),COUNTIF($F$2:F2,F2))

If you want to trap errors...
=IF(COUNTIF($F$2:F2,F2)<=COUNTIF($B$2:$B$7,F2),LARGE(IF($B$2:$B$7=F2,$C$2:$C$7),COUNTIF($F$2:F2,F2)),"")

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Re: using a dedicated cell to choose lookup tables

Thanks Domenic your the best!

Re: using a dedicated cell to choose lookup tables

So if cell C2 change you want to open the file in cell A2. Right? If so I change the target address in the code. Also I put a drop down box in cell C2. The sample workbook is below.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Text <> "" Then
         
        If Target.Address = "$C$2" Then
            With Application.FileSearch
                .NewSearch
                .LookIn = "C:\"
                .Filename = [A2] & ".XLS"
                 Application.ScreenUpdating = False
                If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
                    Workbooks.OpenText .FoundFiles(1), xlWindows
                    ActiveWorkbook.Close
                    Application.ScreenUpdating = True
                End If
            End With
        End If
    End If
     
End Sub

Re: using a dedicated cell to choose lookup tables

I'm sorry I'm trying to undestand what you are trying to achieve. So you are saying you would have to input NY_05 manually in cell A2 then it works. Is that what you are saying?

Re: using a dedicated cell to choose lookup tables

I've taken a look at the last two files you attached and see that you're using formulas that include the INDIRECT function. If I'm not mistaken, I've already indicated in a previous post that the INDIRECT function requires that the file be opened.

But I thought the the idea was to use my formula, without the INDIRECT function, and have Fin's code automatically change the workbook reference for the formulas, as per his first sample file that he attached in this thread. So, for example, the following formulas would be used...

F2, copied down:

=INDEX([NY_05.xls]Sheet1!$D$2:$D$6,MATCH(MIN(IF([NY_05.xls]Sheet1!$B$2:$B$6=D2,IF(ABS([NY_05.xls]Sheet1!$C$2:$C$6-E2)<"0:30:30"+0,ABS([NY_05.xls]Sheet1!$C$2:$C$6-E2)))),IF([NY_05.xls]Sheet1!$B$2:$B$6=D2,ABS([NY_05.xls]Sheet1!$C$2:$C$6-E2)),0))

G2, copied down:

=INDEX([NY_05.xls]Sheet1!$E$2:$E$6,MATCH(MIN(IF([NY_05.xls]Sheet1!$B$2:$B$6=D2,IF(ABS([NY_05.xls]Sheet1!$C$2:$C$6-E2)<"0:30:30"+0,ABS([NY_05.xls]Sheet1!$C$2:$C$6-E2)))),IF([NY_05.xls]Sheet1!$B$2:$B$6=D2,ABS([NY_05.xls]Sheet1!$C$2:$C$6-E2)),0))

...and anytime the filename changes in A2 Fin's code would automatically change the reference for the formulas accordingly. If this is the intent, then Fin would have to adapt his code to your file.

Re: using a dedicated cell to choose lookup tables

Quote from skye9

The file I attached is actually the one I got from you, and I didn't make any changes to the code. It appeared to work perfectly with the lookup table closed using Fin's code. But then it stopped working and I now have to open the lookup table in order to get it to work.

Since the formula contains the INDIRECT function, the file containing the lookup table needs to be opened.

Quote

I see you've coded the actual file name (NY_05) into the formula, but shouldn't the file name be a reference to A2 instead, so that different filenames can be selected via A2?

According to the sample files from Fin (the first ones he attached called Lookups.zip), the filename is hard coded into the formula. Then, when the filename in the specified cell changes, the code automatically changes the filename within the formula. At least that's what I understood. So I think you'll need Fin to help you with the code.

An alternative, as previously mentioned, is to download and install the free add-in Morefunc.xll and use INDIRECT.EXT which allows files to be closed.

Re: using a dedicated cell to choose lookup tables

Quote from skye9

Its very strange that I had the file lookup & selection working before (with the lookup file closed), using both your previous formula and Fin's VBA code, but its mysterious how it no longer works.

I could be wrong, but I can't see how it could work if the formula included the INDIRECT function. Hmmm....

Quote

BTW, Fin did not hard code the filename into his VBA, only the file PATH which included a reference to the cell containing the actual filename (A2).

Oh, I see. I thought you were using his first example. Unfortunately, I don't know how his code works. Hopefully, he'll be able to help. But, in any case, I don't think you'll be able to use the INDIRECT function as part of the formula in conjunction with Fin's code.

Quote

I guess I can explore the world of add-ins and try the morefunc.xll, but I was hoping to get some help in figuring this out and have this working again like before without needing the add-in. But, oh well...

Well, the advantage of using the add-in is that it comes with a variety of other functions which you may find very useful...

Re: using a dedicated cell to choose lookup tables

Did you get my private message?

Re: using a dedicated cell to choose lookup tables

skye9,

Paste this code and let me know if this helps.

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

Re: using a dedicated cell to choose lookup tables

Quote from Fin Fang Foom

Did you get my private message?

I'm assuming that this post is directed to skye9, right?

Re: using a dedicated cell to choose lookup tables

Quote from Domenic

I'm assuming that this post is directed to skye9, right?

Yes its directed to skye9 I'm sorry I should've said so. One more thing I think I know whats the problem is. The code works on my end. But there is a catch. When he uses the drop down list the code triggers on cell C2 and picks up the file name in cell A2. Now if he double clicks anywhere else in the spread sheet it will disappear his vaules. But the last post I did will take care of that problem. But if it does not work for him that means it could be his excel. If you would like to test it out on your end Domenic try the sample workbook below it should work on Macintosh.