Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is it possible to use Autofilter, when column labels not in Row 1?
Hello Excel experts,
I've designed an Excel spreadsheet for work, which consists of summarising figures for the 12 months of the year, with a separate worksheet for each month. On each monthly worksheet, I've used the first four lines to summarise the data contained in the rest of the sheet. The fifth row is used to define the colume data labels, and the actual data is contained in rows six onwards. Since the first row is not being used to define the column labels, it seems I can't use Autofilter, which I've just discovered is perfect for what I need to do: to hide some rows depending on whether a cell contains data or not. Is there a way to get Autofilter when the data doesn't start in the first row? Many thanks, Paul -- paul<dotmoore@pandora<dotbe Adjust "dots" for real address |
#3
|
|||
|
|||
Paul,
The entire table to be filtered must be selected. Autofilter then uses the first row of that selection as the headers. Since you have non-table stuff in the first four rows, you can't select a single cell and let it expand the selection, since the first four rows will get selected. You can if you have an empty row after the first four (which can be hidden). Now you can select a single cell in your table, and the selection will expand. Your data must be contiguous, and have no non-table cells adjacent to it. To use the sheet as is, you'll have to manually select your table. A quick way is to select row 5 (all of it, or just the cells you're using in your table in that row). For the latter, select A5, then Ctrl-Shift-Right . Now Ctrl-Shift-Down, then Data - Filter - Autofilter -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Paul Moore" wrote in message ... Hello Excel experts, I've designed an Excel spreadsheet for work, which consists of summarising figures for the 12 months of the year, with a separate worksheet for each month. On each monthly worksheet, I've used the first four lines to summarise the data contained in the rest of the sheet. The fifth row is used to define the colume data labels, and the actual data is contained in rows six onwards. Since the first row is not being used to define the column labels, it seems I can't use Autofilter, which I've just discovered is perfect for what I need to do: to hide some rows depending on whether a cell contains data or not. Is there a way to get Autofilter when the data doesn't start in the first row? Many thanks, Paul -- paul<dotmoore@pandora<dotbe Adjust "dots" for real address |
#4
|
|||
|
|||
Nick and Earl, Many thanks for your help ( and the very quick response!) A further question: Is it possible to filter such that only rows containing a blank cell are displayed? I suppose I want to do something like Include row if ISBLANK(M7:M300) Thanks again, Paul |
#5
|
|||
|
|||
Paul
If you look at the bottom of the autofilter drop-down, you have the options 'Blanks' and 'Non-Blanks' (If they exist) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Paul Moore" wrote in message ... Nick and Earl, Many thanks for your help ( and the very quick response!) A further question: Is it possible to filter such that only rows containing a blank cell are displayed? I suppose I want to do something like Include row if ISBLANK(M7:M300) Thanks again, Paul |
#6
|
|||
|
|||
Found them Nick. Brilliant!! Thanks again, Paul -- -- paul<dotmoore@pandora<dotbe Adjust "dots" for real address "Nick Hodge" wrote in message ... Paul If you look at the bottom of the autofilter drop-down, you have the options 'Blanks' and 'Non-Blanks' (If they exist) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Paul Moore" wrote in message ... Nick and Earl, Many thanks for your help ( and the very quick response!) A further question: Is it possible to filter such that only rows containing a blank cell are displayed? I suppose I want to do something like Include row if ISBLANK(M7:M300) Thanks again, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Auto Skipping and protected cells | Excel Discussion (Misc queries) |