References between worksheets using IF

[Guest]

Hello,
I'm trying to using cross-references between different worksheets within an
Excel document. Using a Paste Link, I get something like this:

'Active '!B18

The problem is that any empty cells show up as "0" in the target. So I
tried using an IF statement, but it doesn't seem to work. What I want it to
do is show the result only if there is something in the source cell,
otherwise not display anything. Is there a problem with the syntax?

=IF('Active '!B18 = "", "", 'Active'!B18)

 

[Gary]

The syntax looks fine. Does 'Active'!B18 contain a formula?

 

[Pete_UK]

You are missing a space after the second Active:

=IF('Active '!B18 = "", "", 'Active '!B18)

Of course, B18 might actually contain a zero!

Hope this helps.

Pete

 

[Guest]

Your formula is correct, however it references two different sheet names.
'Active ' and 'Active'. Notice one has a space at the end. Try fixing that
and see if it works.

HTH,
Elkar

 

[Guest]

You can use the ISBLANK function to test the cell and see if it is blank.

 

[Guest]

Thanks, I did eventually notice that. Even after that, though, it still
wasn't working, because I had the cell formatted for text. As soon as I
changed it to General, the formula started working.

 

[Guest]

Thanks to everyone who responded. After I corrected the problem with the
extra space, the formula still wasn't working and then figured out that I had
the cell formatted for text. As soon as I changed it to General, the formula
started working.

 

[Philip]

Also take out the space before ""'s and = and the second 'Active ' thus:
=IF('Active '!B18="","",'Active '!B18)
The formula still works with them there, but they are not necessary.

Philip