1

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!

Joe K
  • 85
  • 1
  • 1
  • 9
  • It can't be done with formulas, as it will require INDIRECT, and as you stated, INDIRECT does not work with closed workbooks. You will need vba that opens the workbook hidden and then does the vlookup and assigns the value to the correct cell then closes the hidden workbook. – Scott Craner Sep 14 '17 at 15:03
  • How would I go about doing that? – Joe K Sep 14 '17 at 15:04
  • 1
    Start with this: https://stackoverflow.com/questions/579797/open-excel-file-for-reading-with-vba-without-display. – Scott Craner Sep 14 '17 at 15:06
  • 1
    You are attempting a VLOOKUP on a closed workbook so I'll go out on a limb and assume that you want to retrieve more than a single value from one or more worksheets within that single workbook. Probably several values and perhaps hundreds if not thousands. What's so tough about actually opening the workbook? –  Sep 14 '17 at 15:49

1 Answers1

1

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.

Kresimir L.
  • 2,301
  • 2
  • 10
  • 22
  • Granted I basically just copy/pasted this and changed the other workbook (which is open), but this code didn't appear to do/change anything? Can you elaborate a bit on how it works? – Joe K Sep 19 '17 at 16:19
  • you should somehow run this macro...ether through `Developer tab` =>`Macros`, or assigning this macro to a shape. In order to assign macro to shape, you should right click on a shape, then click on `Assign macro`. after that when you click on a shape, your macro will automatically run. – Kresimir L. Sep 19 '17 at 17:06
  • And also make sure that you set right dynamic part of formula in range `B1` – Kresimir L. Sep 19 '17 at 17:07
  • I ended up changing the organization of my obs workbook a bit, but this still came in handy when I used vba to programatically set the formulas for each cell each time the code is run. Thanks! – Joe K Sep 23 '17 at 19:29