Referencing a Range of Cells w/out all those zeros

[drpeppagrl]

I need to reference an entire sheet(sheet1) on another sheet (sheet2) within the same worksheet, but I don't want all of the blank cells to be referenced with zeros, because then I will need to go through an delete all the zeros. Is there a formula to just have the blank cells referenced as blank cells, instead of annoying zeros??

Thanks a bunch!!
This message was edited by drpeppagrl on 2002-10-11 18:44

 

[Mudface]

Do you mean you've linked to a worksheet from another worksheet or workbook and you're returning zero's? If so, wrap an IF statement around the formula eg -

=IF([Book1]Sheet1!A1,[Book1]Sheet1!A1,"")

Where Book1 is the name of your linked workbook, Sheet1 is the worksheet. You can also supress zero values via the Tool-Options-View tab (uncheck zero values) but it might interfere with the rest of your workbook.

 

[drpeppagrl]

Thanks..I will try that.

 

[drpeppagrl]

Worked great! You are the best Thanks Thanks Thanks!!

 

[Yogi Anand]

On 2002-10-11 18:22, drpeppagrl wrote:
I need to reference an entire sheet(sheet1) on another sheet (sheet2) within the same worksheet, but I don't want all of the blank cells to be referenced with zeros, because then I will need to go through an delete all the zeros. Is there a formula to just have the blank cells referenced as blank cells, instead of annoying zeros??

Thanks a bunch!!
This message was edited by drpeppagrl on 2002-10-11 18:44

Hi drpeppagrl:

Welcome to the Board!

Supplementing suggestion by Mudface ...
Is it the display of 0s that you don't like, or you must replace 0s with blanks --

If it is the display of 0's that you don't want, you have some simple ways of suppressing the display of 0s

1. You can go to TOOLS|OPTIONS|VIEW -- and uncheck ZERO VALUES
this will suppress display of all 0 values
2. If you want to suppress display of 0 values selectively, you can use CUSTOM NUMBER FORMAT ...
[=0]""

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

Yogi

 

[IML]

Just a minor tweak to mudface's
=IF(LEN(Sheet1!A1),Sheet1!A1,"")

will bring over a zero if a zero exists on sheet 1.

 

[drpeppagrl]

Yup it worked. You are awesome!! Saved my weekend.

 

[stevebausch]

Browsing through objects, found this:

ActiveWindow.DisplayZeros = True

Is it useful in this situation? Is it ever useful?

Thanks for the feedback, this got me thinking.
Steve

 

[Yogi Anand]

Hi Steve:

This would be the procedural way of turning the TOOLS|OPTIONS|VIEW ... Zero_Values --

Procedurally, TRUE would select the option (so 0 values would be displayed); False would deselect the option (so 0 values will not be displayed)

The actual values are not changed -- only the viewing of zero values is turned ON or OFF

Regards!

Yogi

 

[stevebausch]

Thanks Yogi, I appreciate your response.

(Now, if everyone else would thank the Wizards when they respond........)