Using INFO("directory") to reference a workbook

[babycody]

As the title suggest I would like to use INFO("directory") to reference a range in another workbook that is in the same folder as the open workbook. Is there a way to do this, and can you provide an example of the correct syntax. I have tried to tweek it in several ways, but nothing I tried seems to do the trick. Thanks!

 

[T. Valko]

As the title suggest I would like to use INFO("directory") to reference a range in another workbook that is in the same folder as the open workbook. Is there a way to do this, and can you provide an example of the correct syntax. I have tried to tweek it in several ways, but nothing I tried seems to do the trick. Thanks!

You can reference the range in the other file directly. You don't need this INFO bit.

How do you intend to use this range? Some functions don't support references to external files that are closed. (SUMIF, COUNTIF, OFFSET)

 

[babycody]

I plan to use it with a VLOOKUP function. I want to use the INFO function so that I can incorporate it into a macro. That way the formula will always reference the folder of the workbook I have open.

 

[VoG]

In VBA you can use ActiveWorkbook.Path

 

[babycody]

How would I incorporate a formula into the VBA you posted?

 

[VoG]

How would I incorporate a formula into the VBA you posted?

I have no idea. Try explaining exactly what you want to do.

 

[babycody]

Let me know if this helps:
=VLOOKUP(A5,INFO("directory")&[printed.xls]Sheet1'!$A:$B,2,0)

 

[VoG]

In VBA that would be something like

Range("Z1").Formula = "=VLOOKUP(A5, " & ActiveWorkbook.Path & "\[printed.xls]Sheet1'!$A:$B,2,0)"

 

[babycody]

Thank you VoG! I had to change it slightly, because there was a space after the A5,, and I had to add an apostrophe in the first quoted area.

Range("D5").Formula = "=VLOOKUP(A5,'" & ActiveWorkbook.Path & "\[printed.xls]Sheet1'!$A:$B,2,0)"