I am using a worksheet to track a project list. Each row (beginning with the 5th row) represents a project. The first 4 rows are headers. Each column represents a piece of information about the project. The columns are grouped into sections so some of the cells in the header rows are merged.



I am having trouble with some data in one particular group of columns where the data in those 5 columns will move to a different row. Other columns in the rows are not affected, and it appears that the data in all 5 columns does stick together.

The spreadsheet is saved in SharePoint and is used by many people to update their projects. The 5 columns in question are particularly important for reporting purposes, so these columns are locked (the rest of the worksheet is not) and the worksheet is protected, but is set to still allow people to sort and filter. (Although, when I checked it, it does allow me to filter, but does not allow me to sort.)

I had thought that perhaps those columns weren't sorting when the rest of the spreadsheet is sorting, but 1) when it's protected, it doesn't seem to allow sorting, and 2) when it's unprotected, those columns do sort with the rest of the spreadsheet.

I had thought that perhaps those columns weren't filtering properly either when protected or not, but I have checked both and it seems to work fine.

I thought that perhaps a partial row had been added, but this is not a case of everything below a certain point being shifted down.

I thought it could be an issue of SharePoint allowing multiple people in and updating the spreadsheet at once, since there are about 20 people who would be updating the status fields on the spreadsheet at some point or another, but these are fields that only 3 people on my team would be using (which is why I have those fields locked and the worksheet protected). For some reason, I don't get asked to checkout and checkin this spreadsheet, but I am far from a sharepoint expert.

I am sorry for the lengthy explanation, but I am at a loss of what could be causing this. Any suggestions would be greatly appreciated!

I'm not sure which phenomenon you're asking about. I see two: a) that the worksheet cannot be sorted while it's protected, even though you specified that sorting should be allowed, or b) that Sharepoint allows you to edit it without making you check it out. Or maybe there's a third that I'm missing.

If it's the second one, I'm not a Sharepoint expert either and anyway this is an Excel forum so it isn't the place you would necessarily expect The Right Answer. But I've worked with Sharepoint ... sometimes ... and I vaguely remember that I could edit a sheet without checking it out, but in such a case I risked conflicting with a teammate. I'm not sure, though.

If it's the matter of sorting while protected, my mind immediately focuses on the fact that you say some cells are merged. I've definitely encountered this; if I merge cells between rows then Excel will not allow me to sort row-wise, and I expect that if I merge cells between columns then it wouldn't allow me to sort column-wise either. I haven't found a way around that.

But if you've merged cells between columns—say D22 and E22 are merged, but not D23 and E23—then I don't think that should prevent you from sorting. If this doesn't already tell you what's going on, then rather than guess I think I need to see the real thing; are you willing to post the workbook? If there's confidential information in there, you may have to sanitize it or go to the work of creating a facsimile instead.