Autofilter header row

My experience has been that the first row is where the controls (down arrow) 
appear when autofilter is invoked. But I've now been sent a spreadsheet (just 
unformatted data in a large table) where the autofilter chooses the second 
row. I've compared the new spreadsheet with previous versions and don't see 
any difference in layout, etc.

What can be causing this problem? Is there something "invisible" that tells 
Excel which is the header row for the data to be filtered?

If I select row 1 and then click autofilter, the controls do appear on row 1 
but otherwise it's always row 2 in this spreadsheet.
0
Utf
4/10/2010 10:33:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1974 Views

Similar Articles

[PageSpeed] 44

I have seen this problem if a column (usually on the right) does not have 
data in the header cell.

Make sure every column has a non-empty header cell!
-- 
Gary''s Student - gsnu201001


"Mister Cul-de-sac" wrote:

> My experience has been that the first row is where the controls (down arrow) 
> appear when autofilter is invoked. But I've now been sent a spreadsheet (just 
> unformatted data in a large table) where the autofilter chooses the second 
> row. I've compared the new spreadsheet with previous versions and don't see 
> any difference in layout, etc.
> 
> What can be causing this problem? Is there something "invisible" that tells 
> Excel which is the header row for the data to be filtered?
> 
> If I select row 1 and then click autofilter, the controls do appear on row 1 
> but otherwise it's always row 2 in this spreadsheet.
0
Utf
4/10/2010 1:34:05 PM
AutoFilter can be applied to any row by selecting the row 1st.



-- 
Regards
Dave Hawley
www.ozgrid.com
"Mister Cul-de-sac" <Mister Cul-de-sac@discussions.microsoft.com> wrote in 
message news:56E34C1F-57AD-4B70-9FEB-605AC17E58C5@microsoft.com...
> My experience has been that the first row is where the controls (down 
> arrow)
> appear when autofilter is invoked. But I've now been sent a spreadsheet 
> (just
> unformatted data in a large table) where the autofilter chooses the second
> row. I've compared the new spreadsheet with previous versions and don't 
> see
> any difference in layout, etc.
>
> What can be causing this problem? Is there something "invisible" that 
> tells
> Excel which is the header row for the data to be filtered?
>
> If I select row 1 and then click autofilter, the controls do appear on row 
> 1
> but otherwise it's always row 2 in this spreadsheet. 

0
ozgrid
4/11/2010 1:53:42 AM
Thanks, upon closer inspection I found that was exactly what had happened.


"Gary''s Student" wrote:

> I have seen this problem if a column (usually on the right) does not have 
> data in the header cell.
> 
> Make sure every column has a non-empty header cell!
> -- 
> Gary''s Student - gsnu201001
> 
> 
> "Mister Cul-de-sac" wrote:
> 
> > My experience has been that the first row is where the controls (down arrow) 
> > appear when autofilter is invoked. But I've now been sent a spreadsheet (just 
> > unformatted data in a large table) where the autofilter chooses the second 
> > row. I've compared the new spreadsheet with previous versions and don't see 
> > any difference in layout, etc.
> > 
> > What can be causing this problem? Is there something "invisible" that tells 
> > Excel which is the header row for the data to be filtered?
> > 
> > If I select row 1 and then click autofilter, the controls do appear on row 1 
> > but otherwise it's always row 2 in this spreadsheet.
0
Utf
4/11/2010 3:46:01 PM
Reply:

Similar Artilces:

HELP!! Header thats not a header
Hello All, I have a spreadsheet that when printed is 7 pages. I put a header that is from cells 1 to 6 and have set it in the page setup in the section sheets-print title - rows to repeat at top cells 1-6. This works perfect, every sheet when printed displays these rows at the top of the page. The problem is I don`t want this to print on the top of page 7. How can I make it so pages 1-6 the rows repeat on top and page 7 it doesn`t. Any help would be greatly appreciated. Thanks, Kevin M You have to print in 2 batches, first the 1-6 pages, then remove this and print the 7th page...

Autofilter
Is it possible to autofilter a single column within a project? I am using Project 2007. Thanks Larry Larry, I am not sure what you mean by Autofilter a single column. Can you give more detail as to what you are trying to do? "External Update" wrote: > Is it possible to autofilter a single column within a project? I am using > Project 2007. > Thanks > > Larry Hello Larry, Sure. Turn on Autofilters and use the drop-down filter only on the single field. The Autofilter icons will appear at each field heading, but you need not use them...

Deleting Rows With Criteria
Hi there, I have data in columns A to BS [numeric and words] I want to delete all rows that do not contain the word Stoke from column K. The word Stoke may be on it's own or in a sentence [of no more than 5 words]. Any suggestions, Thanks, Bob I'd suggest using Data, Filter, Advanced Filter to create a new list of just the stoke rows. In the Criteria box point to a range off somewhere (that you create) that has the same header label as column K and in the cell below this: *stoke* -- Jim Rech Excel MVP "Bob Beard" <anonymous@discussions.microsoft.com> wro...

Paste damages formula in autofilter #3
That doesn't seem to work as the paste sends a #REF into the offset o paste so the formula becomes =IF(OFFSET(#REF!,0,-1)>0,1,0) on the righ which returns the #REF error. Think solution would be easier to solve with a macro executin everytime an update occurs maybe?. There's got be a way to do it cause I have seen a worksheet where i works and I don't believe it is likely it was done with macros. (sidenote, database is a central one used to modify other worksheet using macros. Each of these worksheets uses different sets of filters The data pane on the right records subtotal...

Header of ListView
Hi, I have a Listview control with some items within. This ListView has 3 column A,B and C. A item's are left-aligned. B and C ones are right-aligned. this, only for items... but for header i would like to have a different alignment than their respective items. for example : A, B and C header will be left-aligned so graphically it should be like that : A | B | C | -------------------------------------- Item A1 Item B1 Item C1 Item A2 Item B2 Item C2 .... So how an i do it ? Because when i modify HDITEM structure it's for the full c...

Changing Columns to Rows Question
Is there a way to convert data that is in colums down x x x To rows x x x Then if that can be done, Is there a way to store data in a text file so that it is seperated by a comma like 1,2,3,4,? Actually what I need to do is find a way to save a column of data in a file that would load up in note book in a rod seperated by comma's. Thanks James James Copy your range and use Edit / Paste Special / Transpose. This transposes rows to columns and columns to rows. If you save this as a .csv file, by altering the 'Save as file type' option, you should get what you want. And...

shortcut on autofilter function
i have many columns of data using the autofilter. is there a shortcut that either returns all the fields to the all function or a shortcut that returns all the data to the page thanks One line of code will do this (maybe put in your Personal.xls): Sub Foo() Activesheet.ShowAllData End Sub Then assign it to a New (Macro) Icon in your toolbar. It will always be available on any sheet that has an autofilter range assigned. HTH "derwood" <darren.irvine@gmail.com> wrote in message news:1132654133.294598.246590@g43g2000cwa.googlegroups.com... >i have many columns of data u...

counting last seven in row
I have a database with a row of numbers. A new number is added to th row every day. Does anyone know what to use so that the last seve digits are counted each time without me having to constantly adjust th formula -- judois ----------------------------------------------------------------------- judoist's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1059 View this thread: http://www.excelforum.com/showthread.php?threadid=26292 Hi if you mean summing these values and if you don't have any blank rows in between try: =SUM(OFFSET($A$1,COUNTA(A:A)-1,0,-7)) --...

Printed Header on Email
Operating System: Mac OS X 10.6 (Snow Leopard) Email Client: Exchange Hi, <br> I've just made the switch from a Dell to an iMac. Whenever I printed an email in Outlook, it would have my name as the header of the email on the left hand side. In Entourage, there is no name, just the date on the right hand side. I am in HR and need my name printed on top of the page like it was in Outlook. I'm sure I am overlooking something, so please feel free to call me out and let me know how I can fix this! <br><br>Thanks in advance. <br><br>Angela > This messa...

Header footer question
Hello All I'm having an issue with headers and footers. I don't want a header or footer on the first two pages of a document. At of the end of page 2, which is a TOC, I inserted a page break followed by a continous section break Sure enough, pages 1 and 2 are marked Section 1, and Page 3 is marked Section 2. But if I put a header and footer on page 3 and mark it unlinked to the previous header/footer (there isn't one anyway), a header/footer nonetheless is created on pages 1 and 2. Furthermore, page three begiins counting with number 2, even though I've us...

header footer
How do I go about formatting all my worksheets with the same header/footer without having to do each seperately? I have 25 workbooks with 12 worksheets each, all I want is for each sheet to have a header image. How can I do this? Hi CW Right Click one of the sheets and select "Select All Sheets" form the pop up menu. Now set your header/ footer. This header/ footer will be applied to all the worksheets at once. Thanks Karthik Bhat Bangalore Keep in mind that after you "SELECT ALL SHEETS", you have to do your page setup without going into print preview first. Do...

Lost functionality to copy a formula across rows
Recently I lot the functionality to copy a formula across a group of cells. Normally clicking on the cell will give you a "handle" to allow you to drag across a range of cells. At work we are currently using the 2002 Excel and I had this functionality until just recently. I have been getting several worksheets from other sources--could there be a problem with compatibility? I note that even my older spreadsheets lost this functionality. Help! Maybe: Tools > Options > Edit > allow cell drag and drop > OK Micky "SandieB" wrote: > ...

page set up with blank rows between each entry
I put blank rows between each row which makes it easier to read my spread sheet. When I sort the data the blank row disappears. How do I keep this from happening? Thanks Sandoria wrote: > I put blank rows between each row which makes it easier to read my spread > sheet. When I sort the data the blank row disappears. How do I keep this from > happening? Thanks I'm sure someone will correct me if I'm work, but I don't think what you want to do is possible. As a work around try making you row height greater. Click in the square in the upper left hand corner. This will s...

AutoFilter and Frequency Function
I am trying to see if I can create a frequency function that will change as I autofilter different criteria. I have a file with over 15,000 rows returning data on sales opportunities including key fields I want to filter on: organization, product line, geography and status (Won, Lost, Open). In each case I want to see the change in distribution of "days in funnel" whether I am looking at all the data or just one geography or just one product line. It appears that the Frequency function does not recognize the filtered out data and continues to return values for the entire data set....

flexible paste rows function that inserts the right number of rows
Hi there, I'm trying to set up a spreadsheet such that each day - at the TOP of the table/worksheet (about 5 columns wide and couple hundred rows deep) - additional rows of data can be pasted in by non-Excel users. The trick is that on some days it is five rows, on other days it's 25 rows. Is there a special paste function, method of copying/pasting, or feasible macro that will automatically insert the number of copied rows before pasting so that no data below gets overwritten (and there are no blank rows in between pastes)?? many thanks!!! Marika :) I think I'd jus...

download headers only in outlook
When retrieving my email the first message is automatically displayed. I want to decide which one to display first. I don't want a message to be automatically displayed after downloading...thanks Turn off the Preview or Reading pane in View. "THUNTER" wrote: > When retrieving my email the first message is automatically displayed. I want > to decide which one to display first. I don't want a message to be > automatically displayed after downloading...thanks THUNTER <THUNTER@discussions.microsoft.com> wrote: > When retrieving my email the first...

inserting a number of rows by input in a cell
Is the following possible, if yes, how: I want to fill in a number in a cell, which corresponds to the number of rows that will be inserted on the next sheet on a specific location (between row 8 and 9). The rows that are inserted must be filled with the same formula's as the row(s) above (row 8). the number of rows must be dynamic. Normally I would do this by hand: - insert X rows - select the two rows above those rows - pull down the rows to create the same formula's David McRitchie has a macro that almost does what you want. Instead of using a cell to get the number of rows...

autofilter
hello and good new year i'm on w2k and office 2003 on excel when i do two cols A B 1 =a+1 2 =a+1 etc..... i go to for example 500 and after i make auto filter on A and B if i use autofilter i see Filter mode but if after i do only 10 line and not 500 i have 1 of 500 records found and not 1 of 10. i have two problem : with 500 line i have not the count of record found and if i delete some line i must desable autofilter for have the good count of line thanks for your help Debra Dalgleish as some work arounds at: http://www.contextures.com/xlautofilter02.ht...

Summing filtered rows with 2 conditions
Expensescode is a range for items, Expensesmonth is a range for months. H7:H800 is a range of currency. First, I filter the cell B6 to select a month from the Expensesmonth. What formula do I need to have in cell H5 to do the following: have the sum of all filtered cells in range H7:H800 with all values of Expensescode except those values with relative expensescode of 999 and 888. Any help is appriciated Hi, What is a "relative" expense code? Thanks, Shane Devenshire "Khalil handal" wrote: > Expensescode is a range for items, > Expensesmonth is a range for m...

Autosum with Autofilter
I have a spreadsheet with numerous values in one column and different identifying data in other columns. I can sort the data nicely using autofilter, however, my autosum at the bottom of the values column continues to display the total value of all the rows, not just the ones that I have filtered to view. I need to be able to filter the spreadsheet and to have it autosum only those rows in view on screen. Is this possible? Many thanks Peter Use the SUBTOTAL() function which will work only on filtered rows. -- Regards Ken....................... Microsoft MVP - Excel ...

Removing filing extension from header
I prepare financial statements that contain between 10 and 3 worksheets. I would name the file "March 31, 2005" and use the fil name function in the header to have "March 31, 2005" appear on eac worksheet. When I used Excel 2000, the header would read "March 31 2005". After I upgraded to Excel 2003, the header now reads "March 31 2005.xls". Is there any way to remove the .xls extension from the header. I woul hate to have to manually enter a date on each worksheet. Thanks, Mar -- fixcp ---------------------------------------------------------------...

Prevent functions from changing when new rows are added
I have a worksheet configured as a timesheet, where each row represents a task performed. For any given day, I track the task(s) performed and the hours spent on each task, and there are many functions in the worksheet. The worksheet contains the following columns: Column A: blank column (not used for anything). Column B: series of dates (with a blank row in between each date). Column C: tasks performed, manually entered. Column D: hours worked on the task. Column E: hours worked for the week. There's a function in each cell where, if it's Sunday, a sum of the week's...

Cell content in custom header: How?
I need to show the content of a specific cell in the custom page header when I print my worksheet. I'm sure it can be done because I did it way back in Excel 95 or 97, but I can't find the right search term to locate it in the help, and the wizard guidance only helps me put file/sheet/date/page data values in there. So, how do I get the content of cell $D$1 to appear as part of the page header? Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet.PageSetup .LeftHeader = ActiveSheet.Range("D1").Value End With End Sub 'This is workboo...

Header Date
I selected the calendar icon to customize the header on a worksheet. However, the date that shows on my worksheet is incorrect even though the calendar is correct. It is November 5, 2003, and the date is showing 05/11/2003 on my document. If I change the date on the calendar to be May 5, 2003, then it shows 11-5-2003! It transposes it! Is there a way to reformat the way a date is displayed in a header? I haven't found one. Everything I've found is for the default settings for dates in the cells. Thank you. Carol First, I assume you meant May 11, 2003 not May 5, 2003. ...

Autofilter #11
When Autofilter is "on" the column filtered has the down arrow turned blue. I have difficulty seeing blur. Is there a way to change the color to let's say yellow? i dont think there is a way to change the colour of the drop down arrow there is a way to make the arrow visible / invisible. and there is als a function that would colour that cell (column header) for whic autofilter is on. this is from excel help, to make the dropdown arrow invisible. you ca use this to hide all the column headers that currently do not hav autofilter on. Worksheets("Sheet1").Range(&q...