CODEWITHSUNDEEP
excelsharepoint
reediddy
reediddy

Reputation: 75

Excel "true-false" to checked boxes

I have an Excel spreadsheet that I'm generating from a SharePoint dashboard. It's turning my checkboxes in the SharePoint table to "true" or "false" values in the Excel spreadsheet. I wanted to know if it is possible to turn those "true" or "false" values back into checked boxes, with the appropriate check or uncheck option selected.

Any help at all is appreciated!

Upvotes: 5

Views: 23864

Answers (2)

A.S.H
A.S.H

Reputation: 29332

You can add a checkbox over a given cell, then edit its properties (Format Control --> Control Tab) and set its Cell Link property to the address of the cell; i.e. "B2". If you have too many such boolean cells, the task is tedious so you might need to automate it with VBA.

p.s. I agree with @teylyn that this shouldn't be a good choice if you have a huge column of boolean data; it adds too many shapes which is cumbersome. You should use it if the number of boolean cells is rather limited.

enter image description here

Upvotes: 0

teylyn
teylyn

Reputation: 35915

I strongly advise against using check boxes in Excel if you already have the data as TRUE/FALSE in a cell. Check box controls are meant to be for user forms, and even though they can be placed in the spreadsheet grid, they live in a layer on top of the spreadsheet. Yes, they can be linked to a spreadsheet cell, but this is cumbersome.

Instead of using a macro that inserts a check box for each data row you could use a helper column with a formula along the lines of

=IF([@checkBoxField],"a","r")

Then format the helper column with the Marlett font, which will show the letter "a" as a tick and the letter "r" as a cross.

enter image description here

Applying a formula like this will be much faster than inserting check box controls into each row and linking them to the field cells.

Upvotes: 6

Related Questions