Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting Data messes up Check Boxes

    I have a rather elaborate spreadsheet that assists me in my stock trading (hereafter I will refer to it as the real SS). I have created a mini version of that which I am attaching to this post to help explain the problem.
    I recently added a row of checkboxes (created from the Forms toolbar) to help me with certain calculations (they are in row 6 in the attached sheet); each checkbox links to the cell it is placed on - thus, the checkbox you see in column D is linked to cell D6, that in column G is linked to cell G6, and so forth. As you know, when the checkbox is empty it will return a value of FALSE to the cell and when checked it will return TRUE. Based on this, ceratin calculations are made in rows lower than this (see the sheet). That's all fine and works as expected.
    The problem is this: When analyzing stocks in my real SS, I enter them by their symbol but in no particular order, as illustrated in the attached sheet. Periodically, I need to sort them alphabetically otherwise I lose track of where I entered them on the spreadsheet, and also to have some semblance of order! So I sort in ascending alphabetical order - in the attached sheet, I would select cells D2:J8 and sort these left to right by row 2 (row 1 is not be included - its "static"). The problem is, the checkboxes get sorted but they maintain their links to the original cells they were linked to! For example, after the sort, the stock "aaaa" would move from column I to column D and so would the check box but the checkbox would retain its link to I6. This messes up the whole sheet!!! Moreover, if the box was previously checked, it may become unchecked in its new position & vice versa, and this further messes up calculations in all rows below that!
    Is there a way that after the sort, the check boxes maintain proper cell links (meaning - to the cell where they are now situated, not where they came from) and maintain their checked/unchecked status?
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting Data messes up Check Boxes

    Hi,

    I hope I understand your problem, and suggest that when you are doing the sort, just select the range d2:j5 in the sample given. As the formulas are the same in each column there is no need to sort them, or the check boxes.

    I notice that you have some cells that you do want your users to alter, and as you have Data Validation on them to give warning, you could prevent any data entry by setting the Validation Criteria to custom and entering "" as the formula. However a better way might be by use of Sheet protection. To enable this you should first set up what cells can be changed. To do this, first go to Format, Cells and select the protection Tab. You should see that the locked checkbox is ticked. If not tick it and click OK. Then select the cells you want your users to update, and for those cells only uncheck the locked setting. (If you want to hide your formulas you could click Hidden).

    Now if you go to Tools, Protection, and select protect sheet. You can enter a password if you wish. Now only the cells you want are available for editing.

    Just another little tip that might save some typing, =IF(D6=TRUE,IF(D5=0,"",D5),D4) can be replaced by =IF(D6,IF(D5=0,"",D5),D4). You do not need to enter =True, as it is assumed.

    Hope the above is of assistance.

    Andrew

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Data messes up Check Boxes

    Thanks for your post, Andrew.
    1) Selecting just d2:j5 is inappropriate as that does not include the check boxes. Reason: I would have some of these boxes checked and some unchecked (before the sort), so if I do a sort without including the boxes, I would have to do the checking/unchecking of those boxes all over again (assuming I would remember which ones were which) and moreover, the stocks would move around and land up in a column in which the box may be in a checked/unchecked condition opposite to before the sort. So I would have to do my analysis all over again after the sort!
    2) Actually, in the real SS that I referred to (which is much larger that the brief sample I attached), I do have some constants in lower rows that are specific to each sysmbol and naturally they would need to be sorted, otherwise the whole sheet will become a mish-mash of mixed up data.
    3) I basically created the spreadsheet for myself and put on those data validation, etc. features just for fun - I am still learning the ropes <grin>. (But I will try out your suggestions in due course.) My formulae are likewise simple and help me remember the arguments lest I forget!

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting Data messes up Check Boxes

    Ok, sorry again. Try this and see if you ge what you want. Select the checkboxe, right click and select Format, select properties tab and select don't move or size with cells. This has a slight disadvantage in that if you insert a new row above the boxes you will have to manually move them down.

    Andrew

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Data messes up Check Boxes

    (Shaking his head in disbelief) To think the answer was so simple! Whoever said "Ignorance is bliss" obviously never had a computer.
    Thanks a lot, Andrew!! That was quick and appears to do the job.[img]/S/nuts.gif[/img]

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Data messes up Check Boxes

    Stumped,

    It seems you've solved your problem.

    Have you looked at getting stock prices automatically from the web?

    I've used that myself (20 minute delayed prices, no charge) to huge advantage).

    You have to do according to the stock market you a re looking at. But it's possible, and not really too difficult.

    Is this of interest to you, or anyone>
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Data messes up Check Boxes

    I presume you are referring to the query built into Excel 2000? Yes, I have used it sometimes (and actually, I will be posting a question about that in the recent future as my needs are a bit moe complicated than the plain vanilla way that function does its job). I also subscribe to a real-time quote service, and I also have Datek streamer, Medved quote tracker, etc. so I am pretty well covered as far as getting quotes from the web is concerned. But I am always open to new software, especially freeware <grin>, particularly if it would integrate that data seamlessly into an Excel spreadsheet and help me with my analysis during the day (as I still do this chore manually).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •