0

Project Details

I am currently making a checklist of long-term goals, each goal has difficulty self-assigned to it between 1 and 9, and I want to group these goals by difficulty, either in the one-column or in different tables.

As well as this, I've used a checkbox (Form Control) which I will tick once the goal is completed, which will put a strikethrough the goal as well as italicize it. The general idea is something like this. The goal cell is formatted when the corresponding checkboxvalue is TRUE.

My Issue

My problem is that when I want to add new goals to the list, I will need to manually move and adjust the goal list so that the goals are grouped by difficulty.

I would prefer to add goals to the bottom of the list and then use the sort tool to sort the table by difficulty (lowest to highest), or potentially find a way of adding the goal details to one table and it being grouped by difficulty automatically in another table.

Attempted Solutions

1. When I sort the table by difficulty, the correct goal cell is formatted but the checkbox does not move with the sort, ( Here I sorted from (smallest to largest) to (largest to smallest) to highlight the difference ).

I believed this was because I included the checkbox reference cell inside the table.

2. Knowing this, I moved the checkbox reference cells outside of the table. After sorting the table, the checkboxes now move to the correct cell, however the incorrect goals are crossed out. This makes sense, as the cells the goals are formatted on haven't moved.

3. Changing the reference from locked to unlocked (eg. =$D$2 to =D2 ) does not seem to change the result in either scenario. Neither does using a formula to populate the goal column based on another table containing the list.

Despite knowing all this, I have not been able to figure out a way to correctly move both the checkboxes via sorting, as well as correctly sorting the reference cell, thus the correct goal being crossed off. I suspect the solution will need

  • VBA code (which I have no experience in)
  • a smarter formula for conditional formatting
  • using ActiveX checkboxes (Which I would prefer not to)
  • potentially changing the layout of my whole list into separate same-difficulty tables.
  • Or something really obvious that I'm missing

Hopefully what I'm after isn't impossible, and any would be greatly appreciated.

Cheers :)

caddymc
  • 1
  • 1
  • I tried to replicate the checklist and it seems to be working properly, the setting for the first goal is checkbox's linked cell is locked reference (=$D$2) and the conditional formatting is "=D2=TRUE" applied to "=$A$2:$A$7" (I have 6 goals in the testing). Sorting them by Difficulty Level from smallest to largest seems to move everything correctly so I don't think you need VBA to achieve what you want unless you really want everything to be done automatically in which case, you have to research and provide your code attempt and tell us what's not working for us to help you. – Raymond Wu Aug 14 '21 at 04:26
  • @RaymondWu, Thanks for the comment and your time, it's very appreciated. In my spreadsheet, I have the exact same conditions as you however I'm still having problems. Did your checkbox follow the goal to the correct row after sorting, and after enabling the checkbox, does the correct goal become crossed out? If so, could you send a link to a screenshot or a way for me to inspect your table? Cheers – caddymc Aug 14 '21 at 05:22
  • Yes it does, when you sort, did you include the checkbox and the value? I.e. column A:D. I have no access to my computer currently so that's not possible now. I'll make one if I have the time later. – Raymond Wu Aug 14 '21 at 05:24
  • @RaymondWu Yes I did, https://imgur.com/a/Vf5QpRI Here is the range I sorted over, I've sorted from largest to smallest, the checkbox corresponding to goal 1 stayed in C2 where ideally it should be in C10, however, it is correctly crossing out Goal 1. – caddymc Aug 14 '21 at 05:43
  • so the issue is more like the checkbox arent moving with the sort. I'm not sure if that's the cause but i did resize the checkbox from rectangle (it has a label which i deleted but the shape is still rectangle) to the size of the checkbox itself (which is within the cell) . @caddymc – Raymond Wu Aug 14 '21 at 06:15
  • @RaymondWu, Exactly, I found somewhat of a solution to this in my original post (point 2) however I had more problems after this. Ill try changing the shape of my checkbox. Edit - just tried and this doesnt seem to change anything for me – caddymc Aug 14 '21 at 06:29
  • I just tried with Form Control and it works too (I think the first time I did is Form Control). Check your checkbox's properties tab, Object Positioning setting is "Move but don't size with cell" – Raymond Wu Aug 14 '21 at 06:42
  • [Copy of my attempt](https://docs.google.com/spreadsheets/d/1fmgghJFz8R-g7y-8WOseDW5cdcGxkWGV/edit?usp=sharing&ouid=104625805249432855510&rtpof=true&sd=true) @caddymc – Raymond Wu Aug 14 '21 at 06:45
  • Hey @RaymondWu, I send a request to view the file via your email, cheers – caddymc Aug 14 '21 at 06:56
  • I would just do a summary sheet and use large() to get them in order and index with match to get the names etc. If you look on here I have a solution for top 5 with duplicates which might help you. – Solar Mike Aug 14 '21 at 06:56
  • @caddymc Sorry, not used to Gdrive so I didn't know you need to request for access. I have accepted the request so you should be able to view now – Raymond Wu Aug 14 '21 at 06:58
  • See https://stackoverflow.com/a/56016036/4961700 – Solar Mike Aug 14 '21 at 07:01

0 Answers0