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.
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.
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.
Re: using a dedicated cell to choose lookup tables
Once again Domenic you are the best I've seen.
Thank You!
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:
Can you please let me know how to do this?
Thanks
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!
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?
Re: using a dedicated cell to choose lookup tables
Have a look at the attached files. Does this help?
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...
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
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...
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.
Re: using a dedicated cell to choose lookup tables
yes got it working now.
Thanks
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
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
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).
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
Don’t have an account yet? Register yourself now and be a part of our community!