Referencing cells in a closed workbook

Hi

Can somebody help me out with a reference issue? I want to reference a cell in a closed work book from a particular server. The path looks like this

\\server_name\folder1\folder2\folder3\[workbook1.xlsx]worksheet!$A$2

It doesnt like that format. Im getting #REF.
The internet is only showing me how to do it when I want to reference a worksheet on my desktop or a specific computer. I want to reference worksheets on our network so that its not specifically tied to my computer.

Anyone have any advice for me?

After playing around with it I realize, like you said, that it wasn't the file path that caused the error. Im trying to reference a closed workbook with the function INDIRECT. Basically I want to use a formula similar to this one:

INDIRECT("'"&$A$2&"'!"&B3)

This references a specific worksheet whose name is in cell A2.

I instead would like to reference a closed workbook whose path is in A2. Is there anyway to do this?

INDIRECT always returns an error on reference to a closed workbook.

There are workarounds involving macros, where VBA will open the file or use other somewhat more esoteric techniques to retrieve data from a closed workbook. One such workaround is to use something like INDIRECT.EXT, a plug-in with a VBA user-defined function. Another workaround is using VBA to build the cell reference rather than using INDIRECT; if the value in A2 changes, the code creates a hard-coded cell reference.

I can help out with a VBA solution if you would like to go that direction.

I had looked into indirect.ext when I stumbled upon it by accident on Google. It said that it wasn't compatible with the 64bit version though which is what I have.

I do like the idea of using VBA to build the cell reference. Is that hard? I started using VBA 2 days ago, so I'm very knew at it. I don't wanna take up too much of your time but if you can point me in the right direction to get started I'm sure I can figure it out. Thanks a lot for your time!

I beat this one to death when I wanted to work with closed workbook. I ended up using Active Data Object Database Connection (ADODB). This YouTube video is very helpful:
https://www.youtube.com/watch?v=5GjKpPemO10

YourProgrammingNetwork (his video) also helped me with the SQL statements and other such things for doing what you want. Worth looking into if you don't care to open and close those other workbooks when you want one to have all the info.

Thanks! I'll look into it tonight. I really appreciate it