Using Indirect to return results of a cell

[tidnab312]

I've created (with several variables) several cells that contain a full string of what should retrieve data from a different workbook however it's not working due to my lack of knowledge with INDIRECT and the &

so here's what i'm doing
cell value ='C:\test\[mytest.xlsx]worksheet name'!G20

if I input that data manually it returns the value (in this case a date)

Let's assume The cell that contains that string is A1

I've tried
=INDIRECT(A1)
result #REF or #Name (and the workbook IS open)

I'm wondering if I have to break down the 'path' [worksheet name] and !cell number and use something like this

=INDIRECT("'" & A1 & "'!" & B2) or something?

And to complicate things even more I'll need to append this

=IF(ISBLANK(INDIRECT(a1),"",INDIRECT(a1))
that's pretty self explanatory..

I'm lost

Thanks in Advance

 

[Aladin Akyurek]

If A1 houses

C:\test\[mytest.xlsx]worksheet name

then:

=INDIRECT("'"&A1&"'!G20")

should work if the target book is open.

 

[tidnab312]

Still get the #REF! Error
if I evaluate it it says INDIRECT(""&"c:\test\test.xlsx]worksheet name'&"'!a1")

I had to change the value of the cell to not include the cell name and incorporate it as you showed above but am wondering if I can have the full string embedded in a cell...

But first things first...

 

[tidnab312]

Woops looks like I missed a '
Thanks that worked,
Now I have to figure out how to make the G20 be pulled from a cell value(as the data changes)
and then
I'll work on embedding the =IF(ISBLANK

Thanks Again,
:>

 

[tidnab312]

OK so I got the next piece working (pulling the cell number from the value of another cell.

NOW

I can't get the ISBLANK statement working... thinking it's just syntax or missing ) somewhere

=IF(ISBLANK(INDIRECT("'"&F24 &"'!"&G27),"",INDIRECT("'"&F24 &"'!"&G27))

I've learned a ton on this website and am slowly getting it

 

[Aladin Akyurek]

OK so I got the next piece working (pulling the cell number from the value of another cell.

NOW

I can't get the ISBLANK statement working... thinking it's just syntax or missing ) somewhere

=IF(ISBLANK(INDIRECT("'"&F24 &"'!"&G27),"",INDIRECT("'"&F24 &"'!"&G27))

I've learned a ton on this website and am slowly getting it

Maybe you can work with:

If a number is expected...

=N(INDIRECT("'"&F24&"'!"&CELL("address",G27)))

If text is expected...

=T(INDIRECT("'"&F24&"'!"&CELL("address",G27)))