CODEWITHSUNDEEP
excelexcel-formula
Aamir Rind
Aamir Rind

Reputation: 39709

What is wrong with this Excel Formula?

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: 3458

Answers (4)

mousio
mousio

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

Eder
Eder

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

Szymon Kuzniak
Szymon Kuzniak

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

Sid
Sid

Reputation: 4995

=IF(G6="", "", CONCATENATE("[",INDIRECT("G6"),"]",Sheet1!$A1))

You missed a double quote around G6

Upvotes: 0

Related Questions