Vlookup into Large Data Range in Closed File

AHB37

New Member
Joined
Mar 17, 2011
Messages
6
My problem is this:

I have a large-ish .xls data file (~10k rows, 10 columns) that I download every week and save on my hard drive.

I have a template file that then runs vlookups into this file and summarizes certain information. I would like to be able to dynamically reference this outside data file (be able to change one cell in the template file, and have it update all the file references to a different week's external data file.) All of this while none of the external files are open.

There are about 50 cells that would have to be coded this way.

A couple things I have tried.

-Indirect.ext (through the morefunc addin) cannot do vlookups

-Harlan Grove's Pull Function: Works, but seems prohibitly expensive in terms of calculation time. Each cell is taking more than 5 minutes to calculate, and doing a bunch of cells at once locks up my xls. Does this sounds right for a vlookup onto a range of more than 10k rows or am I doing something dumb?

I wouldn't mind just copying and pasting each file into a tab in the template workbook each week, but the problem is that I have between around 50 of these "template" workbooks that I would like to have updated every time I get a new data file.

Any thoughts? Please let me know if any of the above is unclear, or if you need any more info. Sorry if this has been posted, I did search of google and this forum and didn't spot anything.

Cheers!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Why don't you just do Edit/Links and point to the new data every week?
 
Upvote 0
Boss wants 'push button' simplicity. So pretty mcuh all he is willing to do is change the date in one cell of the spreadsheet, and wants to see all data updated accordingly.

I agree that what you suggest is the most practical, just curious if what he wants can be done even if it requires a bit more work on my end.

Thanks for the response.
 
Upvote 0
Well, just have a Worksheet_Change event in that sheet and change the links to point at the required file in VBA then?
 
Upvote 0
Thanks Glen. I had not heard of a Worksheet_change event.

If understand correctly, all I would need would be a code that did the following.

When date input cell changes ($E$1),

-Update all links to new data file (per new date in cell $E$1)

(I have a cell, $E$3, that contains the full file path of the correctdata file, based on the date in $E$1)


I tried recording a macro that involves updating links, but I'm not sure how to edit it so that it use the value in $E$3 to determine the new file to link to. I also don't know how to use the Worksheet_change functionality.

How time consuming would it be to create this VBA code?

Worksheet_change is not a functionality in excel 2002 correct?

Thank you for humoring my ignorance.

Cheers!
 
Upvote 0
... Worksheet_change is not a functionality in excel 2002 correct?

...
... what makes you say that?

What happens when you right-click the sheet tab? Do you get a context menu appear ... and is there a menu option of View Code there? And what happens if you choose that?
 
Upvote 0
In the sheet code area ( as mentioned in my last post ), put this code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$1" Then
        If Dir(Me.Range("$E$3").Value) = "" Then
            ' file does not exist
            ' show a message
            MsgBox "Latest Data File not found. Links not changed."
        Else
            alinks = ThisWorkbook.LinkSources
            ThisWorkbook.ChangeLink Name:=alinks(1), NewName:= _
                Me.Range("$E$3").Value _
                , Type:=xlExcelLinks
        End If
    End If

End Sub
 
Upvote 0
Thanks so much.

What format the value of cell E3 be? Full path? Or just new file name?
 
Upvote 0

Forum statistics

Threads
1,214,770
Messages
6,121,444
Members
449,033
Latest member
Kcolwell16

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top