Locking a Row...Excel hates me

[Amie709]

Ok, I'll try to provide as much information as I can so you can conceptualize what I'm trying to do and, hopefully, help me PLEASE.

I have an Excel spreadsheet with Columns from A-J and Rows from 1-45 or so. No Rows or Columns are hidden. For discussion purposes, we'll pretend the data relates to book categorization). Row 1 runs the span of Column A-J and contains header information (List Author Last name, First name/Publication date refers to first publication, etc.). Meaning it's one long rectangle from A-J. Row 1 is locked. It stays in place when I scroll down through the list. I want it to stay in place, so that's perfect. 

Row 3 and beyond contain the data-those rows behave like I want them to-they will rearrange themselves by Row when I sort a Column. 

Row 2 is the issue. Row 2 contains the columns for the table (Column A=Author, Column B=Publication date, Column C=Number of pages, Column D=Number of chapters, etc.). I want this Row to do a few different things. First, I want it locked in place-it is locked. It does not move when I scroll down. Second, I want to convert these Column titles (Author, Number of pages, etc.) located in Row 2 into Dropdown boxes for sorting. I can right click and sort that way but I want to be able to do it through Dropdown boxes. So, that's one thing I can't get figured out-how to make them dropdown boxes. BUT, here's the bigger problem-when I sort Columns, Row 2 moves as well. So for example Column E Row 2 says "Book Title" what I want to do is click a dropdown menu beside "Book Title" that will sort all the Rows underneath Row 2 alphabetically by book title. Instead, when I right click to sort alphabetically, the Column sorts alphabetically but Row 2 moves in the sort even though it's locked in place. And whichever Row moves into the position of Row 2 based on alphabetical sorting is now locked instead. 

So, that's my problem: I need Dropdown menus for the Columns in Row 2 and I need Row 2 to stop moving when I sort the table. 

I have Excel for the web and the desktop version and neither one was able to fix the problem. I did try splitting the cells using the desktop version but that meant Row 1 and 2 were duplicated and that's not what I want either.

Any suggestions?? I swear this program hates me lol

[#2 · Accepted solution]

@Amie709 

Select A2:J45 (or to the last used row).

On the Insert tab of the ribbon, click Table.

In the dialog that appears, make sure that the check box "My table has headers" is ticked, then click OK.

 

[#3]

OMG it's so beautiful!! Thank you so much!! It put in the dropdown menu and now it's behaving like I wanted it to!!

[#4]

I have a follow up question I'm hoping you can also help me with, since you are already familiar with what my table looks like. I want one column to have very specific entry options. So, Column D I want the only options to be 'yes' 'no' 'maybe' 'n/a' so nothing else can go in that Column besides those options. But I want to be able to choose more than one option per cell. So, for example D4 may have 'yes' D5 may have 'no' and D6 may have 'yes' 'n/a'-how can I do that??

[#5]

@Amie709 

Excel doesn't have built-in support for that - it requires some VBA trickery. See Excel Data Validation - Select Multiple Items