Dedicated Cell To Choose Lookup Table

  • Re: using a dedicated cell to choose lookup tables


    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! :) :)

  • 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




    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


    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 guys,


    I tested the formula in a workbook in about 60 cells, doing a lookup in a huge file containing (40,000 rows, 7 columns) – I didn’t think it would work, but interestingly it does. :)


    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?


    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

  • 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?



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

  • Re: using a dedicated cell to choose lookup tables


    Domenic,


    both yours and Fin's code work perfectly. No problems at all, just a few side issues, like slow execution time, which I know is due to the size of the file I'm performing the lookup on. I was just wondering if there's some way to speed things up, by tweaking the formula, or perhaps by splitting up the data over several sheets (ie. by date - Jan-Jun, and Jul-Dec) instead of all the data on one sheet. And then use an IF statement for the date search to target the sheet that contains that particular date, thereby eliminating a large chunk of unecessary data to search thru and which to slow things down.


    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 installed Morefunc.xll the last time, but it didn't work for me. And I have a sense that it would not necessarily make things any faster or solve these other issues, but I will try it once more.


    Thanks again for your help!

  • 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


    Fin,


    the code (and named ranges) is the one from your very last sample file that you attached a few posts ago: "DatesTimesList_2.xls.


    Works great, nothing wrong with it. Just wondering if there's a way to speed things up a bit and also lock in the cell values, and avoid having to constantly recalculate the sheet when the cell contents suddenly disappear.

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



  • Re: using a dedicated cell to choose lookup tables


    Thanks FinFF!


    That code is perfect. :)


    And yes, I can divide the file into say 4 separate quarterly sections like you said (Jan-Mar; Apr-Jun, etc). Would that be possible - using the month from the date field to select the appropriate section. I'd prefer to keep the sections in one file if possible, if not, that's ok, the above code is a great help.


    Thanks again for your help.

  • 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!

    Files

    • Sample.xls

      (10.75 kB, downloaded 28 times, last: )
  • 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)))

    Files

    • Sample.xls

      (17.41 kB, downloaded 35 times, last: )
  • 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? :)