List only entries that have values

[MrPez]

Is there any way that I can only display a cell if it has a value? I don't mean by conditional formatting or anything like that.

What I have is a set of data in two columns and about 20 rows. At any one time, there will only be a maximum of 4 or 5 rows that have data in, the others will all be #DIV/0! errors. I am creating a report on another sheet that needs to display the valid entries and I'm hoping there is a way to display them only. I could only think of having a row for each possible entry on the report and using conditional formatting or if statements to keep them blank when they aren't actual numbers, but this would make the report look a bit messy and have big gaps in.

Any thoughts would be really appreciated, I've hit a brick wall!

[Marcol]

Try getting rid of the #DIV/0! errors

e.g.

=IF(ISERR(A1/B1),"",A1/B1)

Then filter your data for Non-Blanks
This could then be your report.

If you want it on a separate sheet, copy the filter result and paste to the desired sheet.

Hope this is of some help.

[MrPez]

Thanks. The only problem with that though, is that without the errors my graphs go wrong as these use the same data.

[Marcol]

Why base your graphs on errors?

[MrPez]

Having errors instead of zeros mean that the graphs don't bother trying to plot those points.

[Marcol]

Are you sure of that?

The charts seem to behave in identical ways, regardless of whether "", 0, or #DIV/0! is used in my version of Excel.

[MrPez]

Sorry, I got the error wrong, it was the #N/A one. On my graphs (excel 2003), this just gets ignored, whereas if the cells are empty or zero it tries to plot along the axis. This = very annoying!
Not to worry though, got it sorted with a macro instead.

In case anyone else has the same problem, here is the code

Please Login or Register  to view this content.