Using a Metrics Sheet to Count Checked Checkboxes Returns One Decimal in Linked Cell

Dennis at AECOM

I am using a metrics sheet to count and sum values in columns of a source sheet, and to display the numbers in same column as the entries, in the top row of the source sheet, by using a link to the metrics sheet cell where the counting and summing is occurring.

All works fine except in columns that are formatted as checkboxes. The COUNT function in the metrics sheet works perfect to count boxes checked, but when I link to this count result back to the source sheet (Link for Cell in Other Sheet), it returns the count plus a decimal and a zero, as in if the count is 16, the result displayed in the first row cell of the checkbox column is 16.0

Any ideas how I can truncate this useless (and somewhat confusing) decimal?

Paul Newcome

Let's try using a formula to create a cross sheet reference and pull it over as a text value.

=INDEX({Cross sheet reference to single cell}, 1) + ""

Adding the plus quote quote at the end will convert it to a text string which will hopefully get rid of the decimal. If it doesn't, maybe we can try a ROUND function.

=ROUND(INDEX({Cross sheet reference to single cell}, 1)) + ""

Andrée Starå

Hi @Dennis at AECOM

I hope you're well and safe!

Strange!

What is the exact formula you're using?

Also, try adding +" at the end of it and see if that solves it.

Did that work?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Dennis at AECOM

Andree,

The formula on the metrics sheet to sum the checkboxes that are checked is as follows:

=COUNTIF({Tracking Sheet Range 4}, 1)

To return the sum to the source sheet from the metrics sheet, I use the "Link from Cell in Other Sheet", as below.

This link returns the correct value, but it adds a decimal and one place to the column that is formatted as a checkbox:

Where would I add the "+"?

Thanks again, Dennis

Paul Newcome

Have you tried adjusting the decimal using the option in the top toolbar?

Dennis at AECOM

Paul, thanks, and yes that was the first thing I tried. The issue is, I believe, that the column is formatted as Checkboxes.

Andrée Starå

@Dennis at AECOM

Happy to help!

Strange!

I tested it, and it works for me.

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Dennis at AECOM

Andree, you mean more screen shots than I posted above?

Here is the destination sheet, where the column is formatted as a checkbox:

And here is the formula in the metrics sheet.

Paul Newcome

Let's try using a formula to create a cross sheet reference and pull it over as a text value.

=INDEX({Cross sheet reference to single cell}, 1) + ""

Adding the plus quote quote at the end will convert it to a text string which will hopefully get rid of the decimal. If it doesn't, maybe we can try a ROUND function.

=ROUND(INDEX({Cross sheet reference to single cell}, 1)) + ""

Dennis at AECOM

Paul, that worked! Now I have to figure out why, and how this works.

Thanks,

Dennis

Andrée Starå

@Dennis at AECOM

Glad you got it working!

I saw that Paul had answered already!

Let me know if I can help with anything else!

Best,

Andrée

Paul Newcome

Happy to help. 👍️