When I link a blank cell in sheet one to another sheet (two) it treats that
cell in sheet two as if it has a 0 in it when calculating an average. No 0
shows in the cell and it does not matter if I check or uncheck the "show a 0
in cells that have a 0 value" advanced option

Instead of a straight link, use something like this:

=IF(Sheet1!A1="","",Sheet1!A1)

This will return a blank if A1 in Sheet1 is blank, and blanks will be
ignored by your AVERAGE formula.

Hope this helps.

Pete

On Sep 23, 12:50*am, DickS wrote:
When I link a blank cell in sheet one to another sheet (two) it treats that
cell in sheet two as if it has a 0 in it when calculating an average. *No 0
shows in the cell and it does not matter if I check or uncheck the "show a 0
in cells that have a 0 value" advanced option

Thanks Pete! That worked for me as well working with a workbook from sheet
to sheet. Now I need to do the same concept, but from different workbooks
into one sheet. Is this possible?

"Pete_UK" wrote:

Instead of a straight link, use something like this:

=IF(Sheet1!A1="","",Sheet1!A1)

This will return a blank if A1 in Sheet1 is blank, and blanks will be
ignored by your AVERAGE formula.

Hope this helps.

Pete

On Sep 23, 12:50 am, DickS wrote:
When I link a blank cell in sheet one to another sheet (two) it treats that
cell in sheet two as if it has a 0 in it when calculating an average. No 0
shows in the cell and it does not matter if I check or uncheck the "show a 0
in cells that have a 0 value" advanced option

Yes, but in front of the sheet name you would have the filename (in
square brackets), and if the "sending" file is closed at the time you
will also need to include the full path, something like:

=IF('C:/temp/[filename1.xls]Sheet1'!A1="","",'C:/temp/[filename1.xls]
Sheet1'!A1)

The apostrophes are not strictly needed in this particular example,
but it does no harm to put them in and they are needed if you have any
spaces in the path, filename or sheetname.

The easiest way to establish the path is to have the "sending" sheet
open when you are building the formula, and you can start to type:

=IF(

At that point click on the window of the other file and click on A1,
and Excel will expand the formula when viewed in the formula bar to:

=IF([filename1.xls]Sheet1'!A1

then you can carry on typing and add:

="","",

and again at this point you can click the other file window and cell
A1 and the formula will become:

=IF([filename1.xls]Sheet1'!A1="","",[filename1.xls]Sheet1'!A1

then all you need to do is add the closing bracket at the end and
press enter. Select that cell again to see the formula in the formula
bar, and then close the other file - the formula will expand once more
to include the full path.

Hope this helps.

Pete

On Sep 23, 5:02*pm, Deb wrote:
Thanks Pete! *That worked for me as well working with a workbook from sheet
to sheet. *Now I need to do the same concept, but from different workbooks
into one sheet. *Is this possible?



"Pete_UK" wrote:
Instead of a straight link, use something like this:

=IF(Sheet1!A1="","",Sheet1!A1)

This will return a blank if A1 in Sheet1 is blank, and blanks will be
ignored by your AVERAGE formula.

Hope this helps.

Pete

On Sep 23, 12:50 am, DickS wrote:
When I link a blank cell in sheet one to another sheet (two) it treats that
cell in sheet two as if it has a 0 in it when calculating an average. *No 0
shows in the cell and it does not matter if I check or uncheck the "show a 0
in cells that have a 0 value" advanced option- Hide quoted text -

- Show quoted text -