Locking a column. Excel, 2002.

I don't know if locking is the correct phraseology and perhaps that is why I 
can't find an answer.

In column A are the numbers, 1, 2, 3, and so on and I want these numbers to 
remain stationary (they are the rank ordering) when I do a sort on another 
column (say H).

-- 
Regards

Ron Badour



0
Sorry2994 (8)
5/18/2009 6:44:45 AM
excel 39879 articles. 2 followers. Follow

5 Replies
549 Views

Similar Articles

[PageSpeed] 46

Select the range that you want to sort, but avoid column A.  Then sort your
data.

If you let excel guess at the range, it'll think that you may want all the
contiguous columns sorted.

Personally, I would sort that column, too.

But I'd fill it with a formula like:

=row()-1
(headers in row 1 and data starts in row 2)

And sort the entire range.

And I would never let excel guess at what I wanted sorted.  If it guessed wrong
and I didn't notice it, my data would be trashed.

Ron Badour wrote:
> 
> I don't know if locking is the correct phraseology and perhaps that is why I
> can't find an answer.
> 
> In column A are the numbers, 1, 2, 3, and so on and I want these numbers to
> remain stationary (they are the rank ordering) when I do a sort on another
> column (say H).
> 
> --
> Regards
> 
> Ron Badour

-- 

Dave Peterson
0
petersod (12005)
5/18/2009 11:27:19 AM
On 18 May, 07:44, "Ron Badour" <So...@NoAddressBuddy.com> wrote:
> I don't know if locking is the correct phraseology and perhaps that is why I
> can't find an answer.
>
> In column A are the numbers, 1, 2, 3, and so on and I want these numbers to
> remain stationary (they are the rank ordering) when I do a sort on another
> column (say H).
>
> --
> Regards
>
> Ron Badour

Hi Ron

Are you selecting the range to sort?

If not, try doing that and then Data > Sort > select "Continue with
the current selection" option if it's displayed > Sort > select your
sort options > OK

I've presumed that there is no formula relation between columns A and
H.

Regards

Steve
0
britwiz (47)
5/18/2009 11:34:22 AM
Dave and Steve

Thanks for your replies.  I am a self taught Excel novice and I don't 
understand your suggestions.  Let me furnish more data.

Col A is the relative standing (first place, second place and so on) and I 
want it to remain stationary when sorting.  Col B is name, col C is wins, 
col D is losses, col E is
total and col F is average (%).  I want to sort on col F and have the 
highest average in 1st place at the top of the list (col A) and the next 
highest in 2nd place and so on.

I went to Data, Sort and selected Column F descending.  I marked "no 
headers"  and clicked OK.  There is no "Continue with the current selection" 
option.  I don't know what you mean by "selecting the range to sort."  There 
is no formula relationship between columns f and a.

-- 
Regards

Ron Badour


"Scoops" <britwiz@hotmail.com> wrote in message 
news:35a1b253-730f-4f6c-b98d-f57b4a9946e8@b1g2000vbc.googlegroups.com...
> On 18 May, 07:44, "Ron Badour" <So...@NoAddressBuddy.com> wrote:
>> I don't know if locking is the correct phraseology and perhaps that is 
>> why I
>> can't find an answer.
>>
>> In column A are the numbers, 1, 2, 3, and so on and I want these numbers 
>> to
>> remain stationary (they are the rank ordering) when I do a sort on 
>> another
>> column (say H).
>>
>> --
>> Regards
>>
>> Ron Badour
>
> Hi Ron
>
> Are you selecting the range to sort?
>
> If not, try doing that and then Data > Sort > select "Continue with
> the current selection" option if it's displayed > Sort > select your
> sort options > OK
>
> I've presumed that there is no formula relation between columns A and
> H.
>
> Regards
>
> Steve 


0
Sorry2994 (8)
5/18/2009 5:26:30 PM
Select your range to sort first:
B1:F999 (assuming your data ends in row 999)
Do not include column A.

Then use data|sort.

Do not let excel guess what range that should be sorted.

Ron Badour wrote:
> 
> Dave and Steve
> 
> Thanks for your replies.  I am a self taught Excel novice and I don't
> understand your suggestions.  Let me furnish more data.
> 
> Col A is the relative standing (first place, second place and so on) and I
> want it to remain stationary when sorting.  Col B is name, col C is wins,
> col D is losses, col E is
> total and col F is average (%).  I want to sort on col F and have the
> highest average in 1st place at the top of the list (col A) and the next
> highest in 2nd place and so on.
> 
> I went to Data, Sort and selected Column F descending.  I marked "no
> headers"  and clicked OK.  There is no "Continue with the current selection"
> option.  I don't know what you mean by "selecting the range to sort."  There
> is no formula relationship between columns f and a.
> 
> --
> Regards
> 
> Ron Badour
> 
> "Scoops" <britwiz@hotmail.com> wrote in message
> news:35a1b253-730f-4f6c-b98d-f57b4a9946e8@b1g2000vbc.googlegroups.com...
> > On 18 May, 07:44, "Ron Badour" <So...@NoAddressBuddy.com> wrote:
> >> I don't know if locking is the correct phraseology and perhaps that is
> >> why I
> >> can't find an answer.
> >>
> >> In column A are the numbers, 1, 2, 3, and so on and I want these numbers
> >> to
> >> remain stationary (they are the rank ordering) when I do a sort on
> >> another
> >> column (say H).
> >>
> >> --
> >> Regards
> >>
> >> Ron Badour
> >
> > Hi Ron
> >
> > Are you selecting the range to sort?
> >
> > If not, try doing that and then Data > Sort > select "Continue with
> > the current selection" option if it's displayed > Sort > select your
> > sort options > OK
> >
> > I've presumed that there is no formula relation between columns A and
> > H.
> >
> > Regards
> >
> > Steve

-- 

Dave Peterson
0
petersod (12005)
5/18/2009 5:36:44 PM
Dave

After fiddling with it for an hour, I finally got it.  Many thanks for the 
hints on how to go about it.
-- 
Regards

Ron Badour



"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4A119CAC.D33B684@verizonXSPAM.net...
> Select your range to sort first:
> B1:F999 (assuming your data ends in row 999)
> Do not include column A.
>
> Then use data|sort.
>
> Do not let excel guess what range that should be sorted.
>
> Ron Badour wrote:
>>
>> Dave and Steve
>>
>> Thanks for your replies.  I am a self taught Excel novice and I don't
>> understand your suggestions.  Let me furnish more data.
>>
>> Col A is the relative standing (first place, second place and so on) and 
>> I
>> want it to remain stationary when sorting.  Col B is name, col C is wins,
>> col D is losses, col E is
>> total and col F is average (%).  I want to sort on col F and have the
>> highest average in 1st place at the top of the list (col A) and the next
>> highest in 2nd place and so on.
>>
>> I went to Data, Sort and selected Column F descending.  I marked "no
>> headers"  and clicked OK.  There is no "Continue with the current 
>> selection"
>> option.  I don't know what you mean by "selecting the range to sort." 
>> There
>> is no formula relationship between columns f and a.
>>
>> --
>> Regards
>>
>> Ron Badour
>>
>> "Scoops" <britwiz@hotmail.com> wrote in message
>> news:35a1b253-730f-4f6c-b98d-f57b4a9946e8@b1g2000vbc.googlegroups.com...
>> > On 18 May, 07:44, "Ron Badour" <So...@NoAddressBuddy.com> wrote:
>> >> I don't know if locking is the correct phraseology and perhaps that is
>> >> why I
>> >> can't find an answer.
>> >>
>> >> In column A are the numbers, 1, 2, 3, and so on and I want these 
>> >> numbers
>> >> to
>> >> remain stationary (they are the rank ordering) when I do a sort on
>> >> another
>> >> column (say H).
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Ron Badour
>> >
>> > Hi Ron
>> >
>> > Are you selecting the range to sort?
>> >
>> > If not, try doing that and then Data > Sort > select "Continue with
>> > the current selection" option if it's displayed > Sort > select your
>> > sort options > OK
>> >
>> > I've presumed that there is no formula relation between columns A and
>> > H.
>> >
>> > Regards
>> >
>> > Steve
>
> -- 
>
> Dave Peterson 


0
Sorry2994 (8)
5/18/2009 9:59:41 PM
Reply:

Similar Artilces:

Problem inserting a column!!!
Hello all. I just accessed a worksheet which Im trying to add a column to... I highlight the column which i want the new column inserted ...and when i try to insert a column it doesnt work! A warning message comes up stating that a column cannot be inserted until blank cells are removed from the right side of the sheet or something to that effect!? Whats going on??? Thank you. -- londonchick ------------------------------------------------------------------------ londonchick's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24529 View this thread: http://www.e...

Automatically Starting a Excel Addin
Hi We are using Excel 2000 and have a Addin that create a extra Tool bar. What we want to do is get this automatically loaded everytime someone loads Excel. If I go to Tool \ Addin and tick our addin it loads everytime OK. But we don't want to get all our users to have to do this themselves, if we know either the regit key or file that we needed to puch out to our users we could turn this setting on for them. Does anyone know how to do this? Thanks Paul www.purenetworking.net Jan Karel Pieterse used a helper workbook that added an addin in his namemanager utility. You can find it a...

leading zeros in excel
I need to display leading zeros in whole numbers eg 05324 Format as 00000 -- HTH Bob Phillips "kiwi" <kiwi@discussions.microsoft.com> wrote in message news:4560E85F-CE45-487F-812F-C7CA9D7FB042@microsoft.com... > I need to display leading zeros in whole numbers eg 05324 If that is for a US zip code, they really should be formatted as text then they can also be consistent with zip+4 codes as well, and with codes used in other countries as well. Format as text is best, or precede with a single quote. If they aren't going to be treated arithmetically then ...

Sort
We've recently made a very long spreadsheet with about 80 columns. BAD things happen! When people do a lazy sort - that is, ctrl home, Sort and use the populated defaults, Excel only "selects" 64 columns. Of course this totally pooches the data and creates disconnects. No problems when the user selects the whole spreadsheet (by selecting the upper lefthand corner), sorts and fills in the criteria themselves. My question is, is this for real or am I imagining it? Is there a limit of 64 columns in Excel on that lazy sort? We're dealing with the workaround, I j...

excel dropdown menu question
Being a complete newbie here, my question is as follows. I have created a spreadsheet for our Little League baseball team by position and inning. In each cell I have created a dropdown menu that lists each of our 11 players by name so that all I have to do is select the players name and it fills in that cell for that position/inning. What I would like this drop down menu to do, if possible, is as I move down the player position (in a single inning) and choose a players name to fill that position, the dropdown menu then removes that players name in the next position to be filled. EX: ...

How can I create a graph & execute Excel from a non- MS environme
I would like to set up an application that can be run from Unix or another operating system that can be used to display a graph in Excel.(maybe a perl script, or something?) I can create a csv file from the data coming from a non-microsoft environment. I would like to be able to then run a unix system command, or a command that can be executed from the "run" line from MS Start menu that would use this data to open Excel and display the data as a chart. CJ Dev Wrote: > I would like to set up an application that can be run from Unix o > another > operating system that...

Conditionally format points based on a third column
I would like the color of my data points to vary based not on their x or y value, but rather on a third column running alongside them. How can I do it? Assuming your third column is column "C" and that you would want to change the colors of the XY points based on the names Jim, Frank, or Kim, a VBA option could look like this (does not include any error handling): Sub ChangeXYColors() Application.ScreenUpdating = False Dim Rng As Range Cnt = 1 For Each Rng In Range("C2:C10") ActiveSheet.ChartObjects("Chart1").Activate ActiveChart.Seri...

excel database 25,000 plus names
I am working with a database i set up in excel. It has over 25,000 names, total size of file is 274mb. If i transfer all my data from an excel database to access, will i work faster. Right now it takes forever to open and save. I am told it is not my computer. any tips??? -- mikedipaol ----------------------------------------------------------------------- mikedipaola's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3568 View this thread: http://www.excelforum.com/showthread.php?threadid=55465 If this is just a datastore, then yes, it is better in Access and wh...

Outlook 2002 Rules Wizard Won't Respond
I got overactive in adding Rules to kill SPAM and now Outlook 2002 is permanently deleting items that I would prefer to see. Problem is that I cannot open the Rules Wizard (it won't respond), either using the button or from the Tools menu. When I go to Add-in manager, there is no way to "install" the Rules Wizard. I'd like to clear all of the rules and start over. Your help would be appreciated. J. Pine ...

Include contact e-mail address column
Is there a way to include the e-mail address column in the contact list by account report? You will need Crystal Reports 9.2.2 installed on a client computer plus the Crystal Enhancements for CRM (from the CRM installation CDs) in order to modify reports. -- Matt Wittemann http://icu-mscrm.blogspot.com "Agnes" wrote: > Is there a way to include the e-mail address column in the contact list by > account report? Thank's Matt. "MattNC" wrote: > You will need Crystal Reports 9.2.2 installed on a client computer plus the > Crystal Enhancements ...

how do I download an excel template%3f
I have tried to download an excel template from the MicroSoft website. However, after clicking the download now button nothing happens. The download never reaches my computer. I am using excel 2003 and have MicroSoft XP on my computer. The information in the following article may help: Downloading Office Online templates http://office.microsoft.com/en-us/assistance/HA011352971033.aspx Dante wrote: > I have tried to download an excel template from the MicroSoft website. > However, after clicking the download now button nothing happens. The > download never reaches my co...

Copy and Paste in Excel, copies cell and formula, but shows same v
Excel 2003 with 2007 converters installed. Not constant problem, but have seen it once before, can't remember how to stop it happening. I have a bank forecast which I have a running total column, against a individual line item list. Noddy stuff. Problem. If I make changes to the forecast I need to recopy down the running total formula to refresh the running total when it doesn't pick up the additions, usual when a cut has been pasted in. Whilst it allows me to dragdown or copy and paste the formula and the new cells take on the correct line/column numbers to do thei...

Excel Formula Copy
Why would a formula copy down the whole column but NOT provide the correct answers within the cells at one computer and at another computer would work? Jennifer schreef: > Why would a formula copy down the whole column but NOT provide the correct > answers within the cells at one computer and at another computer would work? Could you be more specific? What does it say on the other computer? Maybe in Tools > Options> Calculations "manual" is selected. Maybe the Automatic Calculation option is off on the computer it wont work on?? Thats my only guess "Jennifer&quo...

I need more columns
What if you need more than 256 columns? I cannot link the worksheet pages either because I am averaging the numbers 256 is the limit in Excel. You can write an average formula, though it's more work, e.g. =SUM(Sheet1!A1:A500,Sheet2!A1:A500)/COUNT(Sheet1!A1:A500,Sheet2!A1:A500) On Sat, 22 Jan 2005 09:57:04 -0800, "dilbert" <dilbert@discussions.microsoft.com> wrote: >What if you need more than 256 columns? >I cannot link the worksheet pages either because I am averaging the numbers In fact you don't need to "roll your own" average. this for...

unintentional re-sizing of documents embedded in Excel
Within the last few days, Ive been experiencing a problem with Word documents embedded in an Excel worksheet. While I'm typing in the embedded document, the document size will change without any apparent cause. I've been using this form for years without any problem. We have virus protection software that is current, so I doubt that is the problem. Any ideas or suggestioins?? TIA, Rich Hi no solution but this is probably not a virus or a setting. This happens in my experience quite often to embedded Excel documents. Would consider this more as a bug (with no workaround I know...

How to have multiple columns in excel that will expand and colaps.
I have a large about of tiered data, multiple columns, that I would like for the users to be able to expand and colapse on by each column level to view that level of data. Is there a method of doing this. It's hard to understand what you are wanting from what you say. Provide some detail about what the data looks like at the start, then what it looks like after expanding and then what it looks like after collapsing. Don't attach a file to your post. Just describe what you have and want. HTH Otto "columnhelp" <columnhelp@discussions.microsoft.com> wrote in me...

Excel data query goes away.
I have a work book that gets information from an SQL database. About every two months the query goes away. Why? and how can I stop this from happening? ...

insert data from one excel file into another
Is there any way to insert data from one excel file into another without doing copy and pasting? I need to automate the process of inserting data but I cannot use copy-paste, because it puts data on a clipboard, and my Excel VBA program runs in a multi-user environment. So if one instance of a program is copying data into clipboard, and another instance is pasting it at the same time, it will paste incorrect data. Thank you Leonard. You'll need both spreadsheets open, but try this; Option Explicit Sub MyMacro() Dim MyVariable As String Windows("File2.xls").Ac...

Please help with Excel formula problem!
I've spent hours and hours trying to figure this out, with no luck. I'd be PROFOUNDLY grateful if anybody can help. You have several cities, say 10, requesting various amounts of funding. You, the fund source, have a total of $1,500,000 in funds to give them. However, the total amount they're all requesting is, say, $1,655,502. You don't have enough; you only have $1,500,000. 1,500,000/1,655,502 = .9061. So, you grant EACH city 90.61% of what it requested, and all the grants total up to $1,500,000. No problem with that one, you can throw up a little spreadsheet in a few se...

Microsoft Support and Outlook 2002
Why hasn't Microsoft Support chimed in on the multiple instances of Outlook running? john ...

Nuevo excel
Existe una nueva version English ng. Newer than what? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Liliana Uribe" <sluribem@gmail.com> wrote in message news:OrgHvn2yIHA.2068@TK2MSFTNGP05.phx.gbl... > Existe una nueva version Hola Uribe fervientemente que nos dijo:=20 > Existe una nueva version inmediatamente, hasta las prensas que pulsar en este enlace! news://news.microsoft.com/microsoft.public.es.excel --=20 --=20 @+ ;o))) ...

Transfer information into Excel spreadsheet
In Internet Explorer on a particular web page, I used to be able to click on file > Edit with Excel and then select the parts of the page I wanted to export. (By clicking on the small yellow box with a + in it. Now, my "edit" options are with Front Page or Word. What happened? I realize I can right click on the body of the page and select export to Excel, but I want to continue doing it the way I have for the past 6 months. ...

Looking to automatically "unhide" rows in excel using hyperlinks
Using Excel 2000 and want to hide rows then have those rows automatically displayed if a hyperlink is selected. ...

matching part numbers dispersed in two columns
Hello - I work with price lists in Excel. I want to match lists whos main columns are part numbers. Problem is that there are part number that do not match in between the two lists. For example, the part numbers column in one list (I'll call it "A")ma have the part numbers 72579, 72580, 72581, 72582, 72583. The par numbers column in the other list (I'll call it "B") may have 72579 72580, 72582, 72583, 72584. I want to match the numbers from list "B" to list "A". If Excel coul just line up the matching numbers on a new column (and leaving blan ...

How to swap rows and columns?
I had a heart attack last year, and my doctor told me to keep a journal of diet, exercise, etc. I'm using an Excel worksheet to do it. Each column is a day of the year, and the rows are items, like how far I walked that day, how much I ate, how much sleep, my weight, my blood pressure, etc. As I got in the habit of using it, I started adding other stuff, so now it's become an important repository of data for me. The problem is, I thought Excel had essentially unlimited rows and columns, and it turns out that it only has 256 columns, which isn't enough for a year. It has thousa...