I have a group of cells that are mostly 0. I want to display a list of the
cells that aren't 0. I was able to use this formula for the first cell:
=IF(B1140,B114,IF(B1150,B115,IF(B1160,B116,IF(B 1170,B117,IF(B1180,B118,IF(B1190,B119,IF(B1200 ,B120,IF(B1210,B121,0))))))))

I can't figure out how to make the second and third row work.
Any help is greatly appreciated!

here's one way you could do it

Highlite the range, goto, data =filter hit the down arrow and select
custom

enter your criteria

Check out this site for filtering

http://www.contextures.com/xladvfilter01.html


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=520997

Is there a reason you cannot use Autofilter option?
Highlight the Header Row
Click DATA - FILTER - AUTOFILTER
Now click the arrow in the Header of your choice, and select Display "Not 0"

Rgds
Steve

"searcher" wrote:

I have a group of cells that are mostly 0. I want to display a list of the
cells that aren't 0. I was able to use this formula for the first cell:
=IF(B1140,B114,IF(B1150,B115,IF(B1160,B116,IF(B 1170,B117,IF(B1180,B118,IF(B1190,B119,IF(B1200 ,B120,IF(B1210,B121,0))))))))

I can't figure out how to make the second and third row work.
Any help is greatly appreciated!

The cells that I want to diplay the results in are located in a separate
print area of my spreadsheet.

"searcher" wrote:

I have a group of cells that are mostly 0. I want to display a list of the
cells that aren't 0. I was able to use this formula for the first cell:
=IF(B1140,B114,IF(B1150,B115,IF(B1160,B116,IF(B 1170,B117,IF(B1180,B118,IF(B1190,B119,IF(B1200 ,B120,IF(B1210,B121,0))))))))

I can't figure out how to make the second and third row work.
Any help is greatly appreciated!

On Fri, 10 Mar 2006 03:08:27 -0800, searcher
wrote:

I have a group of cells that are mostly 0. I want to display a list of the
cells that aren't 0. I was able to use this formula for the first cell:
=IF(B1140,B114,IF(B1150,B115,IF(B1160,B116,IF( B1170,B117,IF(B1180,B118,IF(B1190,B119,IF(B120 0,B120,IF(B1210,B121,0))))))))

I can't figure out how to make the second and third row work.
Any help is greatly appreciated!

If your data is in the range rng, then this array formula:

=INDEX(rng,SMALL((rng<0)*ROW(rng),ROWS($1:1)+SUM(--(rng=0))))

To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel
will place braces {...} around the formula.

Copy/drag down as far as required to show all the non-zero values.

The above will give a NUM error if you don't have enough non-zero entries to
fill the range. To avoid that, try this array formula:

=IF((ROWS($1:1)+SUM(--(rng=0)))ROWS(rng),"",INDEX(rng,
SMALL((rng<0)*ROW(rng),ROWS($1:1)+SUM(--(rng=0)))))

Note that your criteria is to "display the cells that aren't 0".

The formula above will display any cells in rng that are not 0 OR BLANK.


--ron