CODEWITHSUNDEEP
excelexcel-formulavlookup
Joe K
Joe K

Reputation: 85

Use cell value/contents to determine an external reference file or sheet name?

I'm working on a cell formula that returns (via VLOOKUP?) a cell in a closed workbook (thus I believe INDIRECT will not work). The issue is that I want to use a value in the active sheet to determine the name of the sheet in the reference workbook and can't figure it out. Here's the best I've got.

=VLOOKUP($A3,'[Other Workbook.xlsm]ObsDFW'!$1:$800,COLUMNS($D4:D4)+3)

ObsDAL is the name of one of the sheets in the "Other Workbook". What I can't figure out is how to keep the "Obs" part of that name constant, but take the "DFW" from a cell value.

Using bad code, I want it to be:

=...[Master Statistics.xlsm]("Obs" & A1)'!$1:$800...

If context is helpful, the "Other Workbook" is full of hourly weather observations, separated into one sheet for each of a series of airports. I'm trying to pull this info into another file/workbook so I don't have to specify each airport specifically in the code many times over.

Thanks in advance!

Upvotes: 1

Views: 557

Answers (1)

Kresimir L.
Kresimir L.

Reputation: 2441

You could try this VBA approach. This way you are adjusting Vlookup formula based on your dynamic_part (sheetname)

Sub VlookupClosedWorkbook()
Dim dynamic_part As Variant
dynamic_part = Range("B1").Value 'You should enter in cell B1 dynamic part of sheet name
For x = 3 To Range("A" & Rows.Count).End(xlUp).Row
Range("B" & x).Value = "=VLOOKUP(A" & x & ",'[Other Workbook.xlsm]Obs" & dynamic_part & "'!$1:$800,COLUMNS($D4:D4)+3,FALSE)"
Next x
End Sub

Assign this macro to shape and fire it after you change your dynamic part. When you trigger it for the first time, make sure that you have both Workbooks open.

Upvotes: 1

Related Questions