Excel display in cell?

Question

My problem:
When I reference an empty cell, the cell holding the equation shows a zero
Open a new file:
In cell A1, enter "=A2"
A1 displays a "0"

I want A1 to appear blank when A2 is also blank

Is there an easy way to do this?
I know I can use an If statement but that is a pain
=if(len(a2)>0,A1,"")

But given the complexity of the rest of the worksheets/workbooks, typing that in would be a very big pain.

The file is also being used by simpler minded folk and confusing formulas will make it difficult for them to use the file

Thanks for any help,

If the answer is simply a "no", then just say so.
Writing a macro is out of the question as well.

Answer 1

you are trying to hide those zeros... with no vba, and you already know how to make a good working formula.?



heres some suggestions:

1. supress the zeros (the fastest and easiest)

- click tools

- click options

- in the View Tab

- uncheck the Zero Values checkbox

this works, but the drawback to this is that it applies to the whole sheet.



2.if you have a range of numbers

- highlight the range

- right click on a highlighted cell

- in the number tab

- change the category to custom

- choose any number style

- i just chose this at random



#,##0_);[Red](#,##0)



- then change it to

#,##0_);[Red](#,##0);



the difference is the ; at the end.

1st section is for positive#'s

2nd section is for negative#'s (mine are red)

3rd section is zero, and putting the ; with nothing after it means hide. the 1st sample didnt have the ; so it assumes 0 is positive style...if you already have a number style, then delete everything afetr the 2nd ;



3 conditional formatting

- highlight your data

- click format

- click conditional formatting

- left box cell value is ...middle box is equal to and 3rd box is 0

- then change the format or font to white



hope 1 of these helps

Answer 2

Basically you want to find a row See if you can display a row with a function as determined by large.Or When you get your answer, in the box left to the answer display the answer minus one column will give you the name.So you need to differentiate between val and answer location. You need to modify the answer location to display the name. Program the five cells to display the column next to the answer of the five highest averages. It's been a while since I have played with excel but try one of those solutions

Answer 3

use this is:



=if(A2="","",A2)



so in other words if A2="", then leave it blank if that is true, if its false just put w/e its in A2.



Also did you know that when you a cell, or 'highlight it', make it active, the little black box thingy in the bottom right will let you copy the function in many areas. So you just drag the black thingy accross or down as you wish to do it. So just organise the data in a table like you have the stuff in one side, and the formulas in the others. Just like you do with xy tables.

Answer 4

=IF(ISEMPTY(A2), "", A2)



should do it



For your users:

If the cell is empty, display nothing, else display what's in the cell.