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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,685
Why don't you just do Edit/Links and point to the new data every week?
 

AHB37

New Member
Joined
Mar 17, 2011
Messages
6
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,685
Well, just have a Worksheet_Change event in that sheet and change the links to point at the required file in VBA then?
 

AHB37

New Member
Joined
Mar 17, 2011
Messages
6

ADVERTISEMENT

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!
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,685
... 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?
 

AHB37

New Member
Joined
Mar 17, 2011
Messages
6

ADVERTISEMENT

Nvm. Misread something while looking into Worksheet_change.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,685
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
 

AHB37

New Member
Joined
Mar 17, 2011
Messages
6
Thanks so much.

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

AHB37

New Member
Joined
Mar 17, 2011
Messages
6
Answered my own question. Full Path.

Thanks again, it is working great.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,988
Members
430,100
Latest member
namhnz

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
Top