Linking cells from different workbooks

[tillytee1]

Hi

I've been trying to link cells from one workbook to another and but I'm
having trouble getting the format to copy.

I have conditional formatting on the original cells eg. If cell has no
value entered, the cell is red. Once a value is entered, the background
will revert to white.

However once I copy and paste the link from a cell with NO value to
another workbook, this conditional formatting does not copy over. It
simply puts in a 0. If the original cell does have a value entered, it
copies over fine.

Can anyone help?

ps the workbooks are not shared.

Thanks

 

[Guest]

You could try paste special, this allows you to copy formats. Go to Edit and
select it from there, this then brings up a menu.

 

[tillytee1]

Yep I've been pasting special and then the link.

The weird thing is, I've managed to do it previously on a simila
workbook with last year's data and it worked! I think it is a defaul
formatting problem but just can't suss it!

 

[Dave Peterson]

Formulas don't bring over the formatting (including conditional formatting).
You could apply format|conditional formatting to that cell with the link if you
wanted, though.

And if your formula looks like:
='C:\My Documents\excel\[book2.xls]Sheet1'!$A$1

You'll get a 0 if that sending cell is empty.

You can rewrite your formula to check:

=if('C:\My Documents\excel\[book2.xls]Sheet1'!$A$1="","",
'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1)

(all in one cell)

It's the same thing you see in a simple formula like:
=A1
if A1 is empty, you'll see a 0.
=if(a1="","",a1)
would be the fix for that.

 

[tillytee1]

Thanks Dave, the 'if' function worked.