Dedicated Cell To Choose Lookup Table

Fin Fang Foom · Feb 8th 2006

Re: using a dedicated cell to choose lookup tables

Hey Domenic,

I'm using your formula but its refering to the 2005 file and its not giving me the same results as your displayed. Do you know what I'm missing?

Fin Fang Foom · Feb 8th 2006

Re: using a dedicated cell to choose lookup tables

Never mind I got it.

**Domenic** · Feb 8th 2006

Re: using a dedicated cell to choose lookup tables

Quote from Fin Fang Foom

Never mind I got it.

Great!

**Domenic** · Feb 8th 2006

Re: using a dedicated cell to choose lookup tables

Okay, I've attached two files for you to look at. I've used the INDIRECT function so that you can choose the file you want. As mentioned, your files need to be opened.

One possible alternative is to download and install the free add-in Morefunc.xll and use INDIRECT.EXT. It's suppose to work with closed files, but I can't speak from experience. I've never used it since it's not compatible with my Mac version of Excel.

Another possible alternative is to use the PULL function written by Harlan Grove. You can easily find the code by doing a Google search.

Quote

BTW, I should have considered the use of a time range limiter so that if the time requested is not within a certain reasonable range, then the cell remains empty (but that's ok).

Actually, I don't think that should be a problem. It's just a matter of adding one more condition to the formula. I'll look at it tomorrow morning...

Cheers!

skye9 · Feb 8th 2006

Re: using a dedicated cell to choose lookup tables

Domenic, that is fantastic.

Exactly what I was looking for. And a very simple and clean formula too.

Only thing, using conditional formatting to 'white-out' the #N/As will not work, since I think I'm already maxed out on the CF'g allowed, and besides, the empty cells really need to be "empty" and not contain any data.

And, the time range limiter would be a great addition, so that only the times within say a quarter or half hour away from a match are returned (right now its returning a match for times anywhere within the entire AM or PM range for a date that has only one single time entry).

Thank you so much for your help.

**Domenic** · Feb 9th 2006

Re: using a dedicated cell to choose lookup tables

Okay, I've modified the solution as follows...

1) The formula will leave the cell empty when the criteria is not met.

2) Named ranges have been defined in order to make the formula less cumbersome and more efficient.

3) The time range limiter used is half an hour, as specified by...

"0:30:30"

Notice that the time specified is 30 minutes and 30 seconds. Thirty seconds is added to the range limiter to avoid problems with decimals (time is expressed as a fraction of the day).

So, for example, if you want to change the range limiter to a quarter of an hour, you would use the following instead...

"0:15:30"

See the attached file...

Hope this helps!

skye9 · Feb 9th 2006

Re: using a dedicated cell to choose lookup tables

That's great Domenic!

Works perfectly.

Thank you very much for your help.

(BTW, I'd also like to make use of Fin Fang Foom's VBA code involving drop-down boxes for a similar lookup, and just wondering if I should continue posting here or start a new thread)

**Domenic** · Feb 9th 2006

Re: using a dedicated cell to choose lookup tables

Quote from skye9

That's great Domenic!

Works perfectly.

Thank you very much for your help.

You're very welcome! Glad I could help!

Quote

(BTW, I'd also like to make use of Fin Fang Foom's VBA code involving drop-down boxes for a similar lookup, and just wondering if I should continue posting here or start a new thread)

Good question... I guess if it's related to this post, you could post it here and Fin will automatically get email notification if he's still subscribed to this thread.

Fin Fang Foom · Feb 9th 2006

Re: using a dedicated cell to choose lookup tables

The reason I have'nt respond because I just can't get it to work. When dealing with arrays formulas its a little difficult. The code will transfer the formulas to thoses cells but not as an-array. You would have to double click inside those cells and do a CTRL+SHIFT+ENTER manually. Here is the sample file below if you want to try. But I will still look for a solution that you dont have to it manually. Hopefuly somebody else might give a solution.

skye9 · Feb 9th 2006

Re: using a dedicated cell to choose lookup tables

That's ok Fin Fang Foom.

Domenic's formula works perfectly. But I was really inspired by your VBA code.

Perhaps I can use your code for an alternate lookup function. Could you add another ‘drop-down’ so that I can select a RANGE of Years for a list of Names to lookup specific Dates for (where one drop-down selects a MinYear and another selects a MaxYear)?

Because some names have several Dates, I need a way to specify the Year(s) range that I’d like returned from a variety of lookup files.

In the example below, MinYr and MaxYr are the drop-downs containing the Year values selections and WB2 is one of the workbooks included in the lookup:

(Drp-Dn1 | Drp-Dn2)
MinYr | MaxYr
2004 | 2005

WB1
A |
Bob Roberts
Angela McNeil
Allan Stone

Returns:
Bob Roberts | 3/15/05
Angela McNeil | 2/10/05
Allan Stone | 11/17/04

Thanks a lot, FFF.
You guys are great, and I really appreciate it!

Fin Fang Foom · Feb 9th 2006

Re: using a dedicated cell to choose lookup tables

skye9 Ok I think I got it I use Domenic sample workbook. Look at the sample file below. I Put a drop down box in cell A2 now when you select the files it will open the file and close the file so you will still get vaules base on Domenic formula. Make sure the Costs file are located in the C:\ Drive if you have it the files in a different path make sure change the file path in the code. Take a look at it and let me know if it will do?

Can you post another sample file that I could get a better idea how your data is arrange?

skye9 · Feb 9th 2006

Re: using a dedicated cell to choose lookup tables

Thanks a lot Fin,

That’s perfect! Now I don’t have to open each lookup file when changing from one to another.

Great teamwork!

You and Domenic have provided the foundation for me to tackle other lookup scenarios where the use of drop-downs would come in handy, such as in my last post (doing a lookup only for specific entries in a list by specifying a certain criteria range). I should be able to figure it out, but of course your insights are welcome.

Again, many thanks!

skye9 · Feb 15th 2006

Re: using a dedicated cell to choose lookup tables

Hi Domenic & Fin Fang Foom,

I’m trying to adapt your formulas to another very similar workbook but can’t get it to work. The minor differences are:

C2 | D2 | E2
Filename | Date | Time

But the problem (and major difference) is that the Filename that C2 refers to (the actual Lookup file in the folder) is abbreviated, but C2 will have the original long Filename. Is there a way to use a insert a separate lookup function so that the filename appearing in C2 (ie “Tampa 2005”) is looked up and the corresponding abbreviated file (ie. “TAM_05”) is found and inserted in cell A2 and used as the actual lookup file?

Abbreviated Filename lookup:
A2 | B2 | C2
(Abbr Filename | --- | Filename |

Dallas 2005 | --- | DAL_05 |
New York 2005 | --- | NY_05 |
Tampa 2005 | --- | TAM_05 |

The other difference is that these lookup tables contain multiple columns of data, and so the data I’d like returned may be in the 3rd or 4th (or ?) column over. BTW, the lookup tables’ names (named ranges) are the same as the filenames themselves, and I’m wondering if that’s a bad idea and if it would ever present a problem, warranting that they be renamed.

Thanks for your help.

**Domenic** · Feb 15th 2006

Re: using a dedicated cell to choose lookup tables

Quote from skye9

Is there a way to use a insert a separate lookup function so that the filename appearing in C2 (ie “Tampa 2005”) is looked up and the corresponding abbreviated file (ie. “TAM_05”) is found and inserted in cell A2 and used as the actual lookup file?

Use VLOOKUP for A2...

Quote

The other difference is that these lookup tables contain multiple columns of data, and so the data I’d like returned may be in the 3rd or 4th (or ?) column over.

Can you attach an example?

skye9 · Feb 15th 2006

Re: using a dedicated cell to choose lookup tables

Domenic,

I think the solution is very simple, I'm sure you'll spot it immediately.
I attached the files for you.

**Domenic** · Feb 15th 2006

Re: using a dedicated cell to choose lookup tables

Have a look at the attached file. You'll notice the following...

1) I used VLOOKUP instead of LOOKUP for A2. This will give you an exact match.

2) I've defined names for two columns, Contact and Company.

3) I've modified the formulas in F2 and G2.

Change the range for the defined names, accordingly.

Hope this helps!

skye9 · Feb 15th 2006

Re: using a dedicated cell to choose lookup tables

That did the trick!

Thanks a lot Domenic.

BTW, do you happen to know what the maximum number of rows a lookup table should reasonably contain? I'm wondering what the best way to reference a large file (ie. 20,000 rows) as a lookup table in Excel. I suppose the answer would be to convert the file to Access, but can Excel access such a database file, and do it faster and more efficiently than an Excel lookup table?

Fin Fang Foom · Feb 16th 2006

Re: using a dedicated cell to choose lookup tables

I'm responding to skye9 from a pm. The code in your workbook was not working because you change your file destination. The code triggers in cell A2 but you put your file name in cell C2. I made a very minor adjustment to the code and Domenic formula, becasue his formula is also referecing to cell A2 it should be Cell C2 as well. The sample workbook is below. It should be fine now.

If anyone curios I think this could work on Macintosh also.

Code

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 = [C2] & ".XLS"
                 Application.ScreenUpdating = False
                If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
                    Workbooks.OpenText .FoundFiles(1), xlMacintosh
                    ActiveWorkbook.Close
                    Application.ScreenUpdating = True
                End If
            End With
        End If
    End If
     
End Sub

Display More

skye9 · Feb 17th 2006

Re: using a dedicated cell to choose lookup tables

thanks for your reply Fin.

Yes, I changed the reference to A2 because the lookup filenames represented in C2 are not the actual filenames. A2 references C2 and a lookup table containing a list of the real filenames. The point of all this is to allow the data itself to silently select its corresponding lookup file, and eliminate the need to do it manually (or having to have the lookup files open) with each new data set.

Anyway I had it working fine, except the lookup file needed to be open to work and would not work when closed. But now it doesn't at all. I can't see what the problem could be. I'll go over everything yet again to see if I'm overlooking something obvious. If you have any other ideas, they are of course welcome.

Thanks again!

Fin Fang Foom · Feb 20th 2006

Re: using a dedicated cell to choose lookup tables

Hi Domenic,

Hopefuly you dont mind skye9 but using your workbook as a example. Domenic if you can take a look at the sample workbook below I'm trying to get this formula to work. When you get a chance. This is the current formula I'm using.

=INDEX(C$2:C$15,MATCH(MIN(IF(A$2:A$15=$E$2,IF(B$2:B$15=F2,""))),IF(A$2:A$15=$E$2,IF(B$2:B$15=F2,""))),0)

Dedicated Cell To Choose Lookup Table

Participate now!

Share

Users Viewing This Thread