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
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
hi guys,
I figured out why my lookup function would not work with the lookup file closed, despite Fin Fang Foom's VBA code to access a closed file.
Excel does not recognize A2's reference to another cell which contains the lookup file name. You can only access the closed lookup file by manually typing the file name (ie. "NY_05") into A2. Therefore A2 cannot contain a reference to another cell, only an actual value.
Is there a way around this, like using another cell to do the lookup and have the formula in that cell insert the result (the filename) into A2, so that A2 then contains text?
Thanks a lot for your help.
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.
Re: using a dedicated cell to choose lookup tables
hi Fin,
your code is not the problem. I tried your siggestion but it didn't work. The problem is that somehow Excel is not able to recognize A2's reference to another cell as a value. But if I bypass the referencing and simply type in the actual value into A2, then it works.
C2 = long form file names (ie. "New York 2005")
A2 = actual abbreviated filenames (ie. "NY_05")
J2:K6 = lookup table
A2 contains the lookup formula that displays the abbreviated filenames. Again, Excel is not recognizing the contents of A2 as a 'VALUE'. But when the value is manually typed in (ie. "NY_05"), then it works fine. Thus, A2 needs to contain a value, not a lookup formula.
So is there a way to have the lookup formula in another cell and have it insert the looked up value into A2 so that Excel can see A2 as containing a value?
Thanks
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
YES, exactly!
Short answer:
Excel is not recognizing A2's lookup result (containing the FILENAME to be used for that workbook). Excel will only recognize the filename if it is typed into A2, but not as the result of a lookup. So I need another cell to contain the lookup formula and insert the result into A2.
Long answer:
This is a continuation of the problem I mentioned a week or two ago, getting the lookup function to work with the lookup file closed. I am doing 2 lookups - a lookup to convert a long-form Lookup FILENAME (in C2) to short-form (using table in J2:K6), and secondly, using resulting actual filename (displayed in A2) to be selected as the final lookup file for the actual data in the worksheet. But Excel is not responding to the inital lookup formula in A2, but does respond when I manually type in the results (or if the lookup file is opened).
Lookup 1 = FILENAME CONVERSION
a lookup to convert the long-form lookup file names to their ACTUAL abbreviated form, in order to have Excel SELECT the correct lookup file to use for that particular worksheet.
Lookup 2 = WORKSHEET DATA
once the correct lookup FILE has been selected for the worksheet (as above), that file is then used for the data lookup.
A2 contains
=VLOOKUP(C2,J2:K6,2,0)
Everything works fine except that Excel is not reading and opening the filename that A2 refers to. So, the solution is to use a formula in a separate cell to insert the abbreviated filename into A2.
I think the sample files display it more clearly.
Thanks again.
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
Thanks Domenic.
Re: using a dedicated cell to choose lookup tables
(sorry, sent the last post incomplate by mistake)
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.
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? That's the goal, to be able to have the data select their own specific lookup files from within the worksheet, so that each changing data could point to it's own proper lookup file, without having to do it manually.
Thanks.
Re: using a dedicated cell to choose lookup tables
Quote from skye9The 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.
QuoteI 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
thanks Domenic.
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. 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).
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...
I'm grateful for all your help.
Thanks a lot!
Re: using a dedicated cell to choose lookup tables
Quote from skye9Its 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....
QuoteBTW, 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.
QuoteI 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.
Re: using a dedicated cell to choose lookup tables
Quote from DomenicI'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.