I have four managers who fill in identical sheets, the boss wants one
workbook with links to each managers sheet, so he can check team progress.
The trouble is if there is nothing in the source cell, the link returns 0
which SOMETIMES is valid! What I want is if the source cell is empty to get
nothing and only get 0 if that is what is in the source cell.
On the source sheet I use conditional formatting to highlight an empty cell
if a (logically) related cell has a value in it. (the cells are Job No &
Shift - 0 is a valid shift value)

You'll have to modify your formulas:

with in the same workbook:
=if(sheet1!a1="","",sheet1!a1)

workbook open:
=if([book5.xls]Sheet1!$A$1="","",[book5.xls]Sheet1!$A$1)
or
workbook closed:
=if('C:\My Documents\excel\[book5.xls]Sheet1'!$A$1="","",
'C:\My Documents\excel\[book5.xls]Sheet1'!$A$1)




Graham Y wrote:

I have four managers who fill in identical sheets, the boss wants one
workbook with links to each managers sheet, so he can check team progress.
The trouble is if there is nothing in the source cell, the link returns 0
which SOMETIMES is valid! What I want is if the source cell is empty to get
nothing and only get 0 if that is what is in the source cell.
On the source sheet I use conditional formatting to highlight an empty cell
if a (logically) related cell has a value in it. (the cells are Job No &
Shift - 0 is a valid shift value)

--

Dave Peterson

Dave thanks, is that hungry on resources, I have hundreds of links!

Is it better to link to the result of a calculation or to have the formula
in the summary sheet?

If the choice is keeping the calculation in the original workbook and bringing
back one value

or

bringing back 100's of values and doing the calculation in the "receiving"
workbook

Then I would try to bring back the one value.

And depending on where the workbook is (on a server far away???) and whether
that workbook is open, you may be disappointed how slow it can be.

But if you have no other options, you can open the sending workbook first, then
open the receiving workbook and it'll be very fast.

If I were you, I'd experiment with a few formulas before making a decision.



Graham Y wrote:

Dave thanks, is that hungry on resources, I have hundreds of links!

Is it better to link to the result of a calculation or to have the formula
in the summary sheet?

--

Dave Peterson