Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Monique
 
Posts: n/a
Default How do I use advanced filter to filter for blank cells?

I'm using Excel 2003 for a training database. I have a relatively large
spreadsheet (+50 columns of job titles and +550 rows of SOP's) with various
various data in the cells. Each row must contain data in at least one cell
other that A#. I need to find the rows that contain blank cells from column
B across through last column, so that I can find the gaps. I can't seem to
come up with the correct criteria for this.

Monique
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default How do I use advanced filter to filter for blank cells?

Try this:

With your data list in Cells A5:AX500

B1: Test4Blanks
B2: =COUNTA(B6:AX6)=0

Note: the criteria formula is in B2, but it references the first data row
under the column headings.

Select A5:AX500
<Data<Filter<Advanced Filter
Check: Filter the list, in-place
UNcheck: Unique records Only
List Range: (already selected $A$5:$AX$500)
Criteria Range: $B$1:$B$2
Click the [OK] button

That will display records with no values in Col_B through Col_AX.

Adjust range references to suit your data.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Monique" wrote:

I'm using Excel 2003 for a training database. I have a relatively large
spreadsheet (+50 columns of job titles and +550 rows of SOP's) with various
various data in the cells. Each row must contain data in at least one cell
other that A#. I need to find the rows that contain blank cells from column
B across through last column, so that I can find the gaps. I can't seem to
come up with the correct criteria for this.

Monique

  #3   Report Post  
Posted to microsoft.public.excel.misc
Monique
 
Posts: n/a
Default How do I use advanced filter to filter for blank cells?

Hi Ron,

Thank you! It works.

Monique

"Ron Coderre" wrote:

Try this:

With your data list in Cells A5:AX500

B1: Test4Blanks
B2: =COUNTA(B6:AX6)=0

Note: the criteria formula is in B2, but it references the first data row
under the column headings.

Select A5:AX500
<Data<Filter<Advanced Filter
Check: Filter the list, in-place
UNcheck: Unique records Only
List Range: (already selected $A$5:$AX$500)
Criteria Range: $B$1:$B$2
Click the [OK] button

That will display records with no values in Col_B through Col_AX.

Adjust range references to suit your data.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Monique" wrote:

I'm using Excel 2003 for a training database. I have a relatively large
spreadsheet (+50 columns of job titles and +550 rows of SOP's) with various
various data in the cells. Each row must contain data in at least one cell
other that A#. I need to find the rows that contain blank cells from column
B across through last column, so that I can find the gaps. I can't seem to
come up with the correct criteria for this.

Monique

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
using advanced filter in excel bschrand Excel Discussion (Misc queries) 1 March 14th 06 10:16 AM
advanced filter BorisS Excel Worksheet Functions 1 January 13th 06 06:30 PM
Advanced Filter Chris Excel Discussion (Misc queries) 2 November 10th 05 03:05 PM
Using advanced filter to search for criteria in a list Potatosalad2 Excel Discussion (Misc queries) 1 June 8th 05 03:08 AM
Advanced Filter Problems Brian Excel Discussion (Misc queries) 2 December 20th 04 07:31 PM


All times are GMT +1. The time now is 08:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"