• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Cell link error

C

Cristof

New Member
Hello everybody,

i have a question about an excel cell link.
i want to check from a central workbook, if a specific cell in various different workbook is filled.
if i "hard" link the cells in my central workbook with the other workbooks like
=if('\\server\folder\subfolder\[filename.xlsx]Worksheetname'!A1="";1;-1)
it will work properly.
But i would like to have the same result by concatenating the condition in the if-clause from different cells in the central workbook like
Cell b1: "\\server\folder\subfolder\[filename.xlsx]Worksheetname"
Cell b2: "A1"
Cell b3: =if("'"&B1&"'!"&B2="";1;-1)
But this doesnt work!
I could use the indirect function, but that means i have to open all workbooks that i would to check. And these are a lot. Why does the upper way work properly and the second one does not?
Could anyone explain it to me or help me to find a solution?
Thanks a lot!!!
 
Hi ,

I doubt that there is a way without using VBA ; when you use the hard-coded version i.e. when your formula is this :

=if('\\server\folder\subfolder\[filename.xlsx]Worksheetname'!A1="";1;-1)

the part in bold is a cell reference ; Excel uses this reference to retrieve the value from that cell.

When you use the second version i.e. when your formula is this :

=if("'"&B1&"'!"&B2="";1;-1)

the part in bold is no longer a cell reference ; instead it is a string , which in order to be interpreted as an address , requires the use of the INDIRECT function.

Narayan
 
Hello Narayank991

maybe you have made a good comment. Can you use the address function in excel to solve my problem. Something like
(see above)
=if(address(1;1;1;1;b1)="";1;-1)
Thanky for your help!
 
Hi ,

Sorry , but the ADDRESS function too returns only a string !

Thus :

=ADDRESS(1;1;1;1;"Sheet3")

will return :

Sheet3!$A$1

which is a string ; to retrieve the value from this cell address , will require you to use the INDIRECT function thus :

=INDIRECT(ADDRESS(1;1;1;1;"Sheet3"))

Narayan
 
You must log in or register to reply here.
Back
Top