Need some help in fixing the checklist

[dvm49]

Hello Excel Gurus,

I am looking for some help in setting the checklist in my excel sheet (Data.xlsx). I have been referring to the attached excel file (excel-checkbox-examples '3rd Sheet: ToDo list example') to replicate the creation of a checklist.

Issue1:
Currently, whenever I check the box (Sheet-2 Data.xlsx), the boxes in other cells are also getting checked automatically and also some cell values are getting changed to TRUE / FALSE. I believe it has to do something with the Linked cells but I am not really sure on how to fix this. Some help/guidance here would be helpful. The Linked cells value should not be visible for the users or may be I would prefer to have that Linked cells value in a separate worksheet if that is possible.

Issue2:
In Sheet-1 Data.xlsx, I would like to show the completion percentage graph and value.

Any help on this would be appreciated.

 

[GraH - Guido]

For Issue 1: right click, Format Controls and change cell link. Select the field, and navigate to the sheet and cell reference that you'd like to use.
repeat for each of the check boxes.




Issue 2: what kind of chart are you after? Have you tried to select a cell of the dashboard range followed by ALT + F1? It generates a default chart.

 

[vletm]

dvm49
Why to use ... many ... checkboxes?
Could You use those cells to do same?
I left others almost same.
You can click 'To Do List - checkbox - Status' to change it status.
With [ Reset ]-button, You can reset all to 'To Be Done'.

 

[p45cal]

I should have refreshed this page after leaving it overnight and I would have aseen others had responded!

You have many more checkboxes on your sheet than you you ought to (380+ instead of 240).
Some are sitting exactly on top of others - I've removed the excess ones.

In the attached are 2 offerings, both showing their results on the Dashboard sheet.

Offering 1 involves only your original sheets, linked cells altered to the cell in which the checkbox is sitting and formulae in your Dashboard table. Basic chart below. I've made the font of the linked cells a very light grey for demonstration purposes, you could make the font white.

Offering 2 involves the Data (2) sheet where I've made each checkbox's linked cell a cell in column E of the LinkedCells sheet. In column D of the LinkedCells sheet is a simple formula to covert TRUE/FALSE to a 1/0 which is in turn used to create the pivot table on the Dashboard sheet. The LinkedCells sheet can be hidden.
There is a chart below the pivot table.
Using a pivot table in this way just means you have more scope to display the data in a different ways easily. The downside is that it needs refreshing to update it, although this can be automated.

The attached file is a macro-containing file but macros don't need to be enabled - they're just there to show you some of the things I got up to - they can be deleted.