Curious Excel question

Suburban Rhythm · 10-26-2011, 11:24 AM

This isn't a very important question, but something that is bothering me.

A co-worker has a spreadsheet. About 600 rows, about 85 columns.
This file has multiple users, so it's not surprising things get jacked up.

When co-worker goes to sort, without first highlighting all the data, Excel defaults to begin at row 261. Whether the cursor is in A1, B22 BQ620, it flips back to row 261.

There are gaps, not every row has data in each column, but this occurs prior to, and after, row 261.

As a test, I copied/paste special values into a brand new sheet, and sort works fine, selecting entire bulk of data.

So, the simple solution is just create the new sheet, protect the needed cells, and move forward. But, it's bothering me and I'd like to figure out why it's doing this.

(On a related note, we need an all encompassing Excel thread at some point.)

Rizon · 10-26-2011, 11:53 AM

Is there some type of formatting in row 261? Or hidden rows before/after?

And yeah, there needs to be an official Excel thread!

Suburban Rhythm · 10-26-2011, 12:04 PM

No formatting that I can tell. I copied the formats of 260 down into 261.
All rows/columns unhidden, freeze panes off. No conditional formats.

At some point, I'll try and search through and pull up all the old Excel questions and link together.

Rizon · 10-26-2011, 12:28 PM

Not a table or anything? Or rows 1-260 formatted as something that won't sort with the rest of the data ... like numbers that have been formatted as text? Did you try the sort yourself on that person's file (to rule out user error)?

I'm trying to think of a way that sort would exclude rows. Maybe the rows are locked?

Rizon · 10-26-2011, 12:29 PM

Quote:

Originally Posted by Rizon

Maybe the rows are locked?

I'm pretty sure you can copy locked rows from one spreadsheet into another unprotected spreadsheet and it removes the protection. Maybe that's why it works on yours and not theirs.

* actually I'm pretty certain it does. I had a company that use to send me a protected sheet with locked cells and I'd just copy and paste the data into a new file and it removed all protection, so I could sort/edit as I wished.

Rizon · 10-26-2011, 12:37 PM

I tried to replicate it, by locking the first 260 rows, unlocking the rest and protecting the sheet, then trying a sort and seeing if it excludes the first 260 and starts at 261.

Crashed my Excel

Suburban Rhythm · 10-26-2011, 02:09 PM

Quote:

Originally Posted by Rizon

I tried to replicate it, by locking the first 260 rows, unlocking the rest and protecting the sheet, then trying a sort and seeing if it excludes the first 260 and starts at 261.

Crashed my Excel

So a success, right? haha

My suggestion to her was go ahead and copy/paste and create a new file, and work from there moving forward.

Looking again, no locked cells. I don't think anyone using it would even know how to lock cells. This seems to be one of those cases of a "novice" user doing something by accident that is stumping "expert"...or at least "intermediate" users.

Rizon · 10-26-2011, 02:15 PM

Quote:

Originally Posted by Suburban Rhythm

So a success, right? haha

My suggestion to her was go ahead and copy/paste and create a new file, and work from there moving forward.

Looking again, no locked cells. I don't think anyone using it would even know how to lock cells. This seems to be one of those cases of a "novice" user doing something by accident that is stumping "expert"...or at least "intermediate" users.

I do it to myself all the time! I frequently do some type of keystroke that takes every number (created by formula) in my spreadsheet and displays the decimal out to like 25 numerals. Undo button doesn't undo it and for years I can't figure out what key I've hit.

Suburban Rhythm · 10-26-2011, 03:19 PM

I work in a place now where Excel is...well, was treated like a 4 letter word. Why use that when I can put everything on these papers, and then we can sort through them for hours on end to find the data we need!?!

So, it's a slow transition to getting people to automate functions, link data, etc. I've converted a few people...but still the majority just like to screw up spreadsheets.

Rizon · 10-26-2011, 03:33 PM

Yeah, same here. I work with people that will absolutely not use spreadsheets. They do lists, charts, addition ... all by hand. They make a ton of errors, but refuse to use Excel.

It's painful.

Rizon · 10-26-2011, 03:34 PM

The best was about 11 years ago when I took over accounting for a company. The previous accountant did everything by hand, all the calculations, formulas, etc. Which is cool ... but everything that took her days to do took me minutes, and my work wasn't riddled with math errors.

Pumpy Tudors · 10-26-2011, 03:37 PM

Mmmmm... accountants...

Suburban Rhythm · 10-26-2011, 08:06 PM

Lets make Pumpy an honorary accountant.

I'm working for a charter school now. We can bill home districts for education as well as some ancillary services. I've created tons of forms. Drop Downs, lookups linking to total sheets for school admin to review at a glance. People print the forms and hand write information and slide in my mailbox or under my door.

Back a couple positions ago I worked in a trust tax area of a bank. Similar to your story, everything done by hand. One account, at fiscal year end, was a bundle of paper weighing 10 pounds. It was like I was some sort of evil wizard when it was condensed to one spreadsheet.

The downside was the woman who fell in love with Excel, I caught her one day typing memos in Excel, across cells, rather than open Word.

10-26-2011, 12:04 PM	#3
Suburban Rhythm Pro Starter Join Date: Jan 2002 Location: Pittsburgh	No formatting that I can tell. I copied the formats of 260 down into 261. All rows/columns unhidden, freeze panes off. No conditional formats. At some point, I'll try and search through and pull up all the old Excel questions and link together. __________________ "Do you guys play fast tempos with odd time signatures?" "Yeah" "Cool!!"

10-26-2011, 03:19 PM	#9
Suburban Rhythm Pro Starter Join Date: Jan 2002 Location: Pittsburgh	I work in a place now where Excel is...well, was treated like a 4 letter word. Why use that when I can put everything on these papers, and then we can sort through them for hours on end to find the data we need!?! So, it's a slow transition to getting people to automate functions, link data, etc. I've converted a few people...but still the majority just like to screw up spreadsheets. __________________ "Do you guys play fast tempos with odd time signatures?" "Yeah" "Cool!!"

10-26-2011, 03:37 PM	#12
Pumpy Tudors Bounty Hunter Join Date: Oct 2000 Location: Pittsburgh, PA	Mmmmm... accountants... __________________ No, I am not Batman, and I will not repair your food processor.

10-26-2011, 08:06 PM	#13
Suburban Rhythm Pro Starter Join Date: Jan 2002 Location: Pittsburgh	Lets make Pumpy an honorary accountant. I'm working for a charter school now. We can bill home districts for education as well as some ancillary services. I've created tons of forms. Drop Downs, lookups linking to total sheets for school admin to review at a glance. People print the forms and hand write information and slide in my mailbox or under my door. Back a couple positions ago I worked in a trust tax area of a bank. Similar to your story, everything done by hand. One account, at fiscal year end, was a bundle of paper weighing 10 pounds. It was like I was some sort of evil wizard when it was condensed to one spreadsheet. The downside was the woman who fell in love with Excel, I caught her one day typing memos in Excel, across cells, rather than open Word. __________________ "Do you guys play fast tempos with odd time signatures?" "Yeah" "Cool!!"

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)