Dedicated Cell To Choose Lookup Table

Re: using a dedicated cell to choose lookup tables

Interesting! I just took a quick look at the last two files from Fin and see that the formula does indeed include the INDIRECT. I don't know how the code works, but I'm glad to hear that it works for you.

As far as the add-in is concerned, I'm not sure why that's not working for you. Unfortunately, the add-in is not compatible with my Mac version of Excel so I can't test for you. I know that there can be an issue with this add-in, but if you try searching that website or do a google search you might be able to find some help....

Re: using a dedicated cell to choose lookup tables

Quote from skye9

THANKS Fin Fang Foom!!

I just read your PM & post, and tested your new file, and I'm very happy to report that its working again

I was going insane trying to figure out why it stopped working. Why would it work one day and then just stop working when I went to use it a week later (I never changed anything)?

Thank you so much. And to you too Domenic. BTW Domenic, I installed morefunc.xll but when I opened the file, the lookup function wouldn't work (with the lookup file closed). I didn't see any special instructions for it, but was there something that I needed to do to get it to work?

I really appreciative both your help.
Thanks again!

Display More

I dont use the INDRIECT.EXE because it did'nt work for me. I hear that add-in does give some problems. One of the problems it takes a long time to caculate.

Domenic,

Did the files I provided of my last post worked on your computer?

Re: using a dedicated cell to choose lookup tables

Quote from Fin Fang Foom

Domenic,

Did the files I provided of my last post worked on your computer?

I just finished trying it out. Unfortuntately, I get an error. I guess the code is not compatible with my Mac version of Excel...

Re: using a dedicated cell to choose lookup tables

If you dont mind what error are you receiving?

Re: using a dedicated cell to choose lookup tables

I'm getting...

Quote

Run-time error '445'

Object does not support this action

And yes, I changed the path in your code.

Re: using a dedicated cell to choose lookup tables

Hi Domenic and skye9,

Here are a few things you might want to do.

Change your security settings on your macros. I could explain how but the link below probably explain to you better. I also recommend to check mark:
"Trust access to visual basic prodject" and choose your setting medium.

http://office.microsoft.com/en…tance/HA011189901033.aspx

Now you might want to put your references for your visual basic.

1. Open Excel.
2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
3. On the Tools menu, click References.
4. In the References dialog box, under Available References, Check marked these to select:

(1.) Microsoft Office 10.0 or 9.0 Object Library check box
(2.) Visual basic for applications
(3.) Microsoft Excel 11.0 object library...(2003) ' If you have 2002 then it would be 10.0. If its Excel 2000 then it will be 9.0
(4.) OLE Automation

and click ok save close excel and reopen it.

If these dont work then Excel on macintosh is might causing the problem

Re: using a dedicated cell to choose lookup tables

Hi skye9!

Quote from skye9

I tested the formula in a workbook in about 60 cells, doing a lookup in a huge file containing (40,000 rows, 7 columns)

Wow, that's pretty huge...

Quote

I didn’t think it would work, but interestingly it does.

That's good to know...

Quote

Only problem is that it takes about a minute to execute. And each time I click in a cell or change some other data in the worksheet, it goes into recalc mode and I then have to wait another minute every time. Any suggestions as to how to speed things up a bit?

If you install the Morefunc.xll, you can use the INDIRECT.EXT function which can be set so that it's not volitile. Have you tried to resolve whatever issues you were having? Or is that pretty well a dead issue?

Quote

Domenic, should I be using a different function than this one?

=IF(ISNUMBER(MATCH(MIN(IF(Date=D2,IF(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),IF(Date=D2,ABS(Time-E2)),0)),INDEX(Contact,MATCH(MIN(IF(Date=D2,IF(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),IF(Date=D2,ABS(Time-E2)),0)),"")

Also, you were doubtful about using the INDIRECT function (for the named ranges) - what would you suggest I use for the named ranges? I can open the wb with macros & links off, make the changes, save and close, to prevent cycling thru recalc mode, but I’m hoping there's a better solution. I appreciate any advice.

Thanks

Display More

I may have a couple of thoughts but first how does Fin's code work?

Re: using a dedicated cell to choose lookup tables

May I see the code you are using?

Re: using a dedicated cell to choose lookup tables

With so many array formulas, it's bound to be slow. One thing that may help is eliminating the IF statement. You could then use conditional formatting to hide #N/A error values.

Then you'll need to get rid of the INDIRECT function. I think you'll need to use VBA to point to the specified file. As I've already mentioned, I don't know what Fin's code does or whether it can direct the reference to the appropriate file. However, I know that the first example he attached in this thread did so. Hopefully, Fin can help in this regard.

Otherwise, I know that there's an alternative to using INDIRECT. It's the PULL function by Harlan Grove. It allows files to be closed, but I can't be sure whether it has the same problem with respect to re-calculation. I've never used it so I can't speak from experience. If you want to check it out and find out more, try doing a Google search.

Re: using a dedicated cell to choose lookup tables

Quote from skye9

Another issue of note is that after the lookup has completed and the cells are filled with the data, if I make a non-calculating change (ie formatting change) on the sheet, the cells lose the data and I get ### in those cells - which then requires me to refresh/recalculate the sheet. Is there any way to 'lock' in the data on the sheet so that the cells will retain their calculated values?

I don't know why this is happening. May Fin can shed some light...

Re: using a dedicated cell to choose lookup tables

How about this Copy this code below and paste it in your workbook it should avoid recalculating. It will only calculate if cell C2 is triggered. I think it will be best to keep January and Feburary, March and so on in seperate workbooks, to have 40,000 rows will make things very slow.

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

Re: using a dedicated cell to choose lookup tables

Hey Domenic me again,

Is it possible to have a double criteria in this formula?

=IF(COUNTIF($F$2:F2,F2)<=COUNTIF($B$2:$B$15,F2),LARGE(IF($B$2:$B$15=F2,$C$2:$C$15),COUNTIF($F$2:F2,F2)),"")

I attach a sample file below.

Thanks!

Re: using a dedicated cell to choose lookup tables

Try...

G2, copied down:

=LARGE(IF($A$2:$A$15=E2,IF($B$2:$B$15=F2,$C$2:$C$15)),SUM(($E$2:E2=E2)*($F$2:F2=F2)))

...confirmed with CONTROL+SHIFT+ENTER. If you'd like to trap errors, you can use the following formula instead...

=IF(SUM(($E$2:E2=E2)*($F$2:F2=F2))<=SUM(($A$2:$A$15=E2)*($B$2:$B$15=F2)),LARGE(IF($A$2:$A$15=E2,IF($B$2:$B$15=F2,$C$2:$C$15)),SUM(($E$2:E2=E2)*($F$2:F2=F2))),"")

...also confirmed with CONTROL+SHIFT+ENTER. Or you can use conditonal formatting instead...

Formula Is:

=ISERR(G2)

Hope this helps!

Re: using a dedicated cell to choose lookup tables

Thanks Domenic it works well. I was trying to make this formula to work but i just can't get it. Am I going all wrong with this formula?

=IF(ROW()>ROW(I$2)+COUNTIF($B:$B,F2),"",INDEX(C:C,MATCH(F2,$B:$B,0)+ROW()-ROW(I$2)))

Re: using a dedicated cell to choose lookup tables

Quote

Thanks Domenic it works well.

Your very welcome, Fin!

Quote

Am I going all wrong with this formula?

What are you trying to do?