Reputation: 39689
I have this formula =IF(G6="", "", CONCATENATE("[",INDIRECT(G6),"]","Sheet1","!$A1"))
.
It is reading excel file name
from column G6
, and then it read Column A
from Sheet1
.
This give invalid Cell Reference Error
. Is there is something wrong with this?
Upvotes: 1
Views: 3457
Reputation: 10337
This is what you need:
=IF(G6="", "", INDIRECT(CONCATENATE("'[",G6,"]Sheet1'!$A1")))
If G6
holds the text zipcodes-phoenixTEST.xlsx
, then the formula is equivalent to
='[zipcodes-phoenixTEST.xlsx]Sheet1'!$A1
which will yield the value of cell A1
of Sheet1
of file zipcodes-phoenixTEST.xlsx
…
Upvotes: 3
Reputation: 1884
I've done this in the past,
First you need to have the excel file that you are reading/searching for, open. Then you have to use INDIRECT
after CONCATENATE
:
INDIRECT(CONCATENATE("[", G6, "]", "Sheet1","'!$A1"))
NOTE
You are forgetting '
before !
in "!$A1"
should be "'!$A1"
Upvotes: 0
Reputation: 858
This one worked for me:
=IF(G6=""; ""; CONCATENATE("[";INDIRECT("G6");"]";Sheet1!$A1))
My Excel tells me I should use semicolons (;) in functions (althogh I do not have english version). Also you should not divide Sheet1 and address - they will only work when used together: Sheet1!$A1. This should also not be wrapped in "".
Upvotes: 0
Reputation: 4995
=IF(G6="", "", CONCATENATE("[",INDIRECT("G6"),"]",Sheet1!$A1))
You missed a double quote around G6
Upvotes: 0