Sumif and zero values question

  • Thread starter Thread starter Gordon
  • Start date Start date
G

Gordon

I have a long list of product codes. Against each code is a number obtained
from a sumif calculation from another long list of the same codes. A large
number of these sumif results are zero. Is there a way of automatically
hiding the zero lines such that if one became greater than zero it would
automatically appear in the list?
In other words I would like to be able to automatically only list those
product codes where the sumif result was greater than zero.

(Hope that's clear!)

Thanks
 
Gordon said:
I have a long list of product codes. Against each code is a number obtained
from a sumif calculation from another long list of the same codes. A large
number of these sumif results are zero. Is there a way of automatically
hiding the zero lines such that if one became greater than zero it would
automatically appear in the list?
In other words I would like to be able to automatically only list those
product codes where the sumif result was greater than zero.

(Hope that's clear!)

Thanks
Click to expand...

Sorry, meant to add Excel 2002.
 
One way is to format your cells using the custom number format
0;-0;

This will show positive numbers, negative numbers with a leading - and
blank for zero.

hth

Mike
 
Freemini > said:
One way is to format your cells using the custom number format
0;-0;

This will show positive numbers, negative numbers with a leading - and
blank for zero.

hth

Mike
Click to expand...

I obviously was as clear as mud! It's not a question of the cell format,
what I'm after is some method of hiding completely the lines that return
zero, so that although the original list contains lines with a zero result,
the only lines that are visible are ones with a result greater than zero,
with no spaces between them. Basically what I'm after is like a database
query result, but permanently on, if that makes sense!
 
If the value is on a row all by itself, then add code to hide the row i
the value is 0.

if sheet1.cells(1,1).value = 0 then
sheet1.rows("1").hide
end if

If the value is not all by itself, the code could get considerably mor
complicated. You cannot hide the cell, however, you can move th
contents to the bottom of the page and then hide the rows. Then othe
codes would need to be written to re-move the cell values and re-inser
into the appropriate place. Hope this information helps you
 
Calligra > said:
If the value is on a row all by itself, then add code to hide the row if
the value is 0.

if sheet1.cells(1,1).value = 0 then
sheet1.rows("1").hide
end if

If the value is not all by itself, the code could get considerably more
complicated. You cannot hide the cell, however, you can move the
contents to the bottom of the page and then hide the rows. Then other
codes would need to be written to re-move the cell values and re-insert
into the appropriate place. Hope this information helps you.
Click to expand...

Thanks - yes each value is on a separate row, but now the silly question -
where do I "add the code"?

Ta!
 
You would either add it on the worksheet (goto F11 to open VB and the
double click on the worksheet that you are attempting to hide the row
and then determine if you want the code to active on change, on shee
deactivation or what... and then add the below code
 
Calligra > said:
You would either add it on the worksheet (goto F11 to open VB and then
double click on the worksheet that you are attempting to hide the row)
and then determine if you want the code to active on change, on sheet
deactivation or what... and then add the below code.
Click to expand...

Thanks for the Info - unfortunately in Excel 2002, F11 seems to open a
chart, not VB!
 
Back
Top