How to combine external + local path

[Rezzy777]

I have a cell in a closed workbook I need to refer to.
But the cell address for that workbook is on Activesheet.

So I need to somehow build / combine a formula like this:

='[ExternalFile.xlsx]Sheet1'! & Activesheet.Range("A1")

Activesheet.Range("A1") contains the line "C200",
which is the cell I need to refer to.
So I'll probably need the "INDIRECT" function as well...

Does anyone know how to build this formula?

[Glenn Kennedy]

Exactly what are you trying to do? INDIRECT does NOT work with closed workbooks.

On second glance: is this actually a VBA question??

[Rezzy777]

I'll try to explain differently:

I need to build a path to a cell in a closed workbook.
I can refer to that cell directly using this line (which actually works):

Please Login or Register  to view this content.

The problem in this line is the $C$200 part, since on Activesheet
I have cell $A$1 which contains the value "$C$200".
How can I combine the (closed) workbook path with this cell value?
Do I really need INDIRECT for this (since it doesn't work with closed workbooks)
or is there another way?

Why do you think I have a VBA question?
I posted this in the "Excel Formulas & Functions" section, right?

[Glenn Kennedy]

There is no need for sarcasm... (which is how your comments read to me), as many people post in the wrong place.

I was confused by this "Activesheet.Range("A1")" which made me think it might be about VBA. I still don't know what Activesheet is...

Please post a sample sheet SHOWING what you are trying to do.

[KOKOSEK]

Agreed with Glenn. Sounds sarcastic.
As I understand that reference cell address is located in cell A1 on some sheet (Activesheet at this moment) and OP wants to get value from external, closed xlsx file.
As INDIRECT does not works with closed files, I do not see possibility to get it by formula.

[Rezzy777]

There is no need for sarcasm... (which is how your comments read to me), as many people post in the wrong place.

I was confused by this "Activesheet.Range("A1")" which made me think it might be about VBA. I still don't know what Activesheet is...

Please post a sample sheet SHOWING what you are trying to do.

Your post actually sounded sarcastic to me to start with,
since you were using CAPITALS and multiple question marks ;-)
But my apologies if I sounded sarcastic, and thank you for your comments.
I now know I need to find an other way into this!

PS: I accidentally presumed Activesheet was a well know term on this forum
(since I do a lot of VBA posts indeed).
Activesheet is just the sheet you are currently working on, that's all ;-)

[AliGW]

It is well-known in the VBA section where the VBA experts hang out. It won't be well-known here in this section!

For what it's worth, I did not read your comment as sarcastic, taking into account that your first language probably isn't English, but I can see why others might have read it as such. However, I don't see that there was any sarcasm in Glenn's preceding post, either. He was just trying to clarify what you really wanted, and I do agree that your opening post was ambivalent. As Glenn said, many people post in the wrong place, either accidentally or in error.

[Rezzy777]

I actually didn't think there was such a difference between those sections,
so thank you for pointing that out! I'll remember that next time I need to post a question.

(English isn't my first language indeed ;-) )