Extracting data from rows where one field is blank
 

I'm trying to write a formula or use filtering to get a list of uncleared
checks in a check register. The data looks like this:
21 A B C
22 Date Amount Cleared
23 xxxxx 1.98 X
24 xxxxx 2.55
25 xxxxx 3.00 X

I need to extract the information for check 124, which has not cleared.
I've tried using the IF and ISBLANK functions in an advanced filter dialgue,
but can't seem to make it work. In the criteria cell, my formula is
=IF(ISBLANK(C23),"",B23).
Thanks

Extracting data from rows where one field is blank
 

If you're trying to extract column B if C is blank (as you stated), your
formula is backwards:
=IF(ISBLANK(C23),"",B23) should be =IF(ISBLANK(C23),B23,"").


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=570782

Extracting data from rows where one field is blank
 

Hi BillBurton57

You can use AutoFilter on column C
Choose Blanks in the dropdown


--
Regards Ron de Bruin
http://www.rondebruin.nl



"BillBurton57" wrote in message ...
I'm trying to write a formula or use filtering to get a list of uncleared
checks in a check register. The data looks like this:
21 A B C
22 Date Amount Cleared
23 xxxxx 1.98 X
24 xxxxx 2.55
25 xxxxx 3.00 X

I need to extract the information for check 124, which has not cleared.
I've tried using the IF and ISBLANK functions in an advanced filter dialgue,
but can't seem to make it work. In the criteria cell, my formula is
=IF(ISBLANK(C23),"",B23).
Thanks

Extracting data from rows where one field is blank
 

Tried that... didn't work

"pikapika13" wrote:


If you're trying to extract column B if C is blank (as you stated), your
formula is backwards:
=IF(ISBLANK(C23),"",B23) should be =IF(ISBLANK(C23),B23,"").


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=570782

Extracting data from rows where one field is blank
 

That will only display the blanks, but I need to EXTRACT the rows with blanks
so I can add them up. Summing the amount column keeps the total even if I
only display the blank cells.

"Ron de Bruin" wrote:

Hi BillBurton57

You can use AutoFilter on column C
Choose Blanks in the dropdown


--
Regards Ron de Bruin
http://www.rondebruin.nl



"BillBurton57" wrote in message ...
I'm trying to write a formula or use filtering to get a list of uncleared
checks in a check register. The data looks like this:
21 A B C
22 Date Amount Cleared
23 xxxxx 1.98 X
24 xxxxx 2.55
25 xxxxx 3.00 X

I need to extract the information for check 124, which has not cleared.
I've tried using the IF and ISBLANK functions in an advanced filter dialgue,
but can't seem to make it work. In the criteria cell, my formula is
=IF(ISBLANK(C23),"",B23).
Thanks

Extracting data from rows where one field is blank
 

If you use data|filter|autofilter, then you can change your =sum(...) formula to
=subtotal(9,...)

=subtotal(...)
will ignore those rows hidden by autofilter.

BillBurton57 wrote:

That will only display the blanks, but I need to EXTRACT the rows with blanks
so I can add them up. Summing the amount column keeps the total even if I
only display the blank cells.

"Ron de Bruin" wrote:

Hi BillBurton57

You can use AutoFilter on column C
Choose Blanks in the dropdown


--
Regards Ron de Bruin
http://www.rondebruin.nl



"BillBurton57" wrote in message ...
I'm trying to write a formula or use filtering to get a list of uncleared
checks in a check register. The data looks like this:
21 A B C
22 Date Amount Cleared
23 xxxxx 1.98 X
24 xxxxx 2.55
25 xxxxx 3.00 X

I need to extract the information for check 124, which has not cleared.
I've tried using the IF and ISBLANK functions in an advanced filter dialgue,
but can't seem to make it work. In the criteria cell, my formula is
=IF(ISBLANK(C23),"",B23).
Thanks





--

Dave Peterson