CODEWITHSUNDEEP
vbaexceldynamicvlookup
agentfl
agentfl

Reputation: 13

Microsoft Excel 2007 - How do I write a dynamic Table_Array for Vlookup

I'm not sure if I need to post this question in a specific place, but here goes.

WARNING: Wall of text. I need to explain my situation fully as it appears to be a unique problem with not many solutions.

TL;DR - Master spreadsheet grabs data from "weekly" spreadsheet via vlookups. A new spreadsheet is released every week. How do I automate the vlookup to grab data from a new spreadsheet, every week?

Now here is the long version...

I have a master spreadsheet that uses vlookups pointing to another spreadsheet. Example code below:

=VLOOKUP(A2,'...OTC\2016\Reports Sent\[SALES BY SKU STORE wk 3 (retail) (2).xls]SKU'!$D$1:$G$65536,4,FALSE)

Great. Easy Peasy.

The master spreadsheet is currently pointing to data for "Week 3". Hence, [SALES BY SKU STORE wk3 (retail)(2).xls]. Currently in the file directory, where the spreadsheet lives, week 3 spreadsheet is the most up-to-date. Every week, a new spreadsheet will be saved in the same folder but called [SALES BY SKU STORE wk4 (retail)(2).xls]. Getting the idea now..?

I want the master spreadsheet to be able to point to the new weekly data, everytime a new spreadsheet exists. So one would think to somehow make the vlookup Table_Array dynamic. How do I accomplish this?

Additional note:- I have written a PowerShell script to output all the filenames & file extensions into a .csv file. Then I have created a macro to import the .csv data into a separate sheet in the master file. I did this as a potential lookup table. Example data below, we'll call it "Filename List":

[SALES BY SKU STORE wk1 (retail)(2).xls]

[SALES BY SKU STORE wk2 (retail)(2).xls]

[SALES BY SKU STORE wk3 (retail)(2).xls]

Solutions attempted but failed because my excel/macro/VBA knowledge is very limited:-

Please can anyone shed some light as to how I do this? If you can answer with an idea, I will try my best to emulate it. I will appreciate anything at this point.

Thank you.

Upvotes: 0

Views: 139

Answers (2)

Rory
Rory

Reputation: 34075

Something like this - I've assumed you will never have more than 53 financial weeks:

Sub updateWorkbook()
    Dim n As Long
    Const csFOLDER_PATH As String = "C:\your path\"

    For n = 53 To 1 Step -1
        If Dir(csFOLDER_PATH & "SALES BY SKU STORE wk" & n & " (retail)(2).xls") <> vbNullString Then
            FileCopy csFOLDER_PATH & "SALES BY SKU STORE wk" & n & " (retail)(2).xls", csFOLDER_PATH & "SALES BY SKU STORE CURRENT (retail)(2).xls"
            Exit For
        End If
    Next n
End Sub

You can call this from the Open event of the master workbook. In the ThisWorkbook module, add:

Private Sub workbook_open
updateWorkbook
End Sub

Upvotes: 1

powiedz
powiedz

Reputation: 11

Can you use

=VLOOKUP(A2,INDIRECT("'...OTC\2016\Reports Sent\[SALES BY SKU  STORE wk " & A1 & " (retail) (2).xls]SKU'!$D$1:$G$65536"),4,FALSE)

where A1 is the week number you want to import the data from?

Upvotes: 0

Related Questions