Dynamic Vlookup referencing file name tied to a dropdown menu

[AllMB]

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.

 

[Scott T]

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?

 

[AllMB]

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.

 

[Scott T]

Re: Dynamic Vlookup refencing file name tied to a dropdown menu

To use indirect, requires file to be open.

Excel 2010

	J	K	L	M	N	O	P	Q	R	S	T
cell7
A1:B15	By Division and Budget Category'!
'[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>
[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]46[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]47[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]49[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]51[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J23[/TH]
[TD="align: left"]=VLOOKUP(J21,INDIRECT(T52),2,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]T52[/TH]
[TD="align: left"]=CONCATENATE("'[February 2018 Raw Data File.xls]",T48,Q48)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2010
	A	B
1	cell1	1
2	cell2	2
3	cell3	3
4	cell4	4
5	cell5	5
6	cell6	6
7	cell7	7
8	cell8	8
9	cell9	9
10	cell10	10
11	cell11	11
12	cell12	12
13	cell13	13
14	cell14	14
15	cell15	15
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.

 

[AllMB]

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.

 

[Scott T]

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
	J	K	L	M	N	O	P	Q	R	S	T
17											January
18											February
19											March
20											April
21	cell7										May
22											June
23	7										July
24											August
25											September
26											October
27											November
28											December
29
30
31											February
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48								A1:B15			By Division and Budget Category'!
49
50
51
52											'[February 2018 Raw Data File.xls]By Division and Budget Category'!A1:B15
Sheet1

Cell Formulas
Range	Formula
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.

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.

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

 

[AllMB]

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.

 

[Scott T]

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

 

[AllMB]

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.