Dynamic Vlookup referencing file name tied to a dropdown menu

AllMB

New Member
Joined
Mar 16, 2018
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello All,

I am trying to use a Vlookup that when I change an option from the dropdown menu the table changes the range ( A1:A15 to A17:A24) in the source file for the tab and base file name are the same but February will change to March, then April.

I am trying to use this formula pieced together from reading forums but still having no luck. The Concantenate formula returns the right words to find the file and tab but I cannot get it to work inside the Vlookup.

=CONCATENATE("'[February 2018 Raw Data File.xls]",T48,Q48), T48 is tab name (By Division and Budget Category) and Q48 is the range (A1:A15)

=VLOOKUP(J21,T52,2,False) This is my Vlookup where T52 is the concatenate formula in cell T52.

Still working on getting the February to change March and such.

I get an error: #REF


Any help you guys can give would be great. Also read it might need to be done in VBA.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Re: Dynamic Vlookup refencing file name tied to a dropdown menu

Your concatenate formula results in text. To use text as a reference you would need to use the indirect function. The Indirect function does not like closed workbooks will the workbook always be opened?
 
Upvote 0
Re: Dynamic Vlookup refencing file name tied to a dropdown menu

Scott T,

The spreadsheets will not always be open but if they need to be open they can be. If there is a quick option that will work for the spreadsheets being open that would be great.

Also if you know of an option that will work when the spreadsheets are closed that would be even better. From what I have read it may require VBA code.
 
Upvote 0
Re: Dynamic Vlookup refencing file name tied to a dropdown menu

To use indirect, requires file to be open.

Excel 2010
JKLMNOPQRST
21cell7
22
237
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48A1:B15By Division and Budget Category'!
49
50
51
52'[February 2018 Raw Data File.xls]By Division and Budget Category'!A1:B15

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
J23=VLOOKUP(J21,INDIRECT(T52),2,0)
T52=CONCATENATE("'[February 2018 Raw Data File.xls]",T48,Q48)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


Excel 2010
AB
1cell11
2cell22
3cell33
4cell44
5cell55
6cell66
7cell77
8cell88
9cell99
10cell1010
11cell1111
12cell1212
13cell1313
14cell1414
15cell1515
By Division and Budget Category




You have some options. You could have an open event in this file to open the file February 2018 Raw Data File.xls whenever the file with the indirect is open. You could have code that open the other file and does the lookup and then closed the file.
 
Last edited:
Upvote 0
Re: Dynamic Vlookup refencing file name tied to a dropdown menu

Scott,

Thank you I got the formula to work. Do you know of any way I can tie the word "February" to "March" to "April" and so on in (February 2018 Raw Data File.xls) to a drop down list that is already on the page that other calculation reference? I tried it once and the concatenate formula did not want to work properly.

Also any help in the code that would open up the correct file as the month changes would be greatly appreciated. I can do most formulas but the coding is where my skills are lacking.

Thank you again for your help Scott.
 
Upvote 0
Re: Dynamic Vlookup refencing file name tied to a dropdown menu

You can use data validation to get the month and change the concatenate formula to pull the month name from the drop down list


Excel 2010
JKLMNOPQRST
17January
18February
19March
20April
21cell7May
22June
237July
24August
25September
26October
27November
28December
29
30
31February
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48A1:B15By Division and Budget Category'!
49
50
51
52'[February 2018 Raw Data File.xls]By Division and Budget Category'!A1:B15
Sheet1
Cell Formulas
RangeFormula
J23=VLOOKUP(J21,INDIRECT(T52),2,0)
T52=CONCATENATE("'[",T31," 2018 Raw Data File.xls]",T48,Q48)



use Alt+F11 to open the VBA editor

In the thisworkbook module

Change the path to where your files are. This will open the raw data file when the other file is open.
Code:
Private Sub Workbook_Open()
Dim wb As Workbook
Set wb = ThisWorkbook
fname = Mid(Range("T52"), 3, Application.Search("raw", Range("T52")) - 4)
Workbooks.Open "C:\Users\usernameDesktop\" & fname & " Raw Data File"

wb.Activate
End Sub

This is need if you change the month name drop down. This is a change even and need to go on the sheet with the drop down.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wb As Workbook
Set wb = ThisWorkbook
If Not Intersect(Target, Range("T31")) Is Nothing Then 'the range here should be where your dropdown for month name is
fname = Mid(Range("T52"), 3, Application.Search(" ", Range("T52")) - 3)
Workbooks.Open "C:\Users\tengs\Desktop\" & fname & " 2018 Raw Data File"
wb.Activate
End If
End Sub

The file will need to be saved as a macro enabled file like .xlsm
 
Upvote 0
Re: Dynamic Vlookup refencing file name tied to a dropdown menu

Wow. Thanks Scott. I haven't done any VBA coding, could you help explain what the VBA code is doing?

Also do you know of a formula that will return and range name ($A$1:$E:$13) based on looking up an item from the same drop down menu and return the correct range? Column A:E will not change but the amount of rows per search criteria could change each month but it does not happen often. Is there a way to stop the formula from returning too many rows once it finds a certain word? The word that is at the bottom of each range it "Total" it is one row down from where I would want the formula to stop at.
 
Upvote 0
Re: Dynamic Vlookup refencing file name tied to a dropdown menu

Private Sub Workbook_Open()

Dim wb As Workbook 'this declares wb as a variable that is a workbook

Set wb = ThisWorkbook 'this sets wb to the workbook that the macro is in.

fname = Mid(Range("T52"), 3, Application.Search("raw", Range("T52")) - 4) 'this is a mid and search like in excel. it extracts text starting from the 3rd position in T52 and goes until it finds raw and then goes back 4 positions. This gives you the month and year of the file name

Workbooks.Open "C:\Users\usernameDesktop" & fname & " Raw Data File" 'This opens the file based on fname from above

wb.Activate 'This makes the active workbook the original workbook
End Sub


For your second question could you post a sample of your data.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim wb As Workbook 'same as above
Set wb = ThisWorkbook 'same as above

'The line below check if the cell that was changed is T31, that is the cell with your drop down
If Not Intersect(Target, Range("T31")) Is Nothing Then 'the range here should be where your dropdown for month name is

fname = Mid(Range("T52"), 3, Application.Search(" ", Range("T52")) - 3) 'same as above
Workbooks.Open "C:\Users\username\Desktop" & fname & " 2018 Raw Data File" 'same as above
wb.Activate 'same as above
End If
End Sub
 
Upvote 0
Re: Dynamic Vlookup refencing file name tied to a dropdown menu

Scott,

I can post an example of the data but I would have to scrub the data first. I can get you a good example of one department with the changes from January to February. Also show the table I am working with to make my formulas work. Give me a day or two to get you the data and show my formulas.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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