General Question

winblowzxp's avatar

Omitting columns in Excel 2010 sort?

Asked by winblowzxp (456 points ) February 10th, 2013

I made a spreadsheet for a convenience store for their beer prices. I have columns which are only calculated values based on formulas and I have them protected so that they can’t be disturbed.

I want them to be able to sort by the name and all user modifiable data to change with it, and leave the calculated columns out of the sort. I thought there was a way to do this, but I haven’t messed with anything like that since before the Ribbon.

Observing members: 0 Composing members: 0

6 Answers

elbanditoroso's avatar

highlight the columns you want to make disappear

right click on one of them

choose HIDE from the context menu,]

Voila…

BhacSsylan's avatar

Or, simply highlight the columns you want to sort, and don’t highlight the calculated columns. Then sort, and they should be left undisturbed.

winblowzxp's avatar

The hide option is not available when the sheet is protected. When I highlight the column that I want to sort by, Excel gives me a nice message that says, “Excel found data next to your selection, but you do not have sufficient permissions to change those cells.”

I need to be able to sort the data while the sheet is protected. The people who will be using it only know how to enter data and sort it. That’s about it. I have some long formulas that I don’t need getting accidentally deleted.

BhacSsylan's avatar

Huh, I have no worked with protected sheets, I’m sorry :-/

CWOTUS's avatar

Though I can’t imagine why you wouldn’t want to sort the calculated fields that are based on the data to be sorted, there is a way to accomplish what you want, but it may involve more “permissions” problems.

You can create a macro that will remove sheet protection temporarily, allow the user to sort (in ways that you have pre-defined), and then re-apply the protection. The problem is that some users may not even have rights to run the macro.

winblowzxp's avatar

No worries. I haven’t worked with protected sheets much either.

I don’t need to sort the calculated fields because they use the same formulas. I’ll play with the macros and see what I can muster. I’ll also try VBA.

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther