Copying a Checkmark Field from one to another on the same sheet.

Hello, I have a process set up where I pull data in from another sheet, and then I have a handful of Columns with formulas that will fill with a Check or Uncheck based on the information in the data that was pulled in.

My goal was to be able to use these Checkbox columns to track processes, but since the columns are locked to a formula, they are unable to be edited. I realized I would need to create a whole new column that copied the information from the formulated column, and I tried using Automation to do so, but whenever the columns change it doesn't change the new columns. I attached the Automation below. The VPN* field is the one with the formula column, the VPN field is the one I want to copy it.

Thanks all

Find more posts tagged with

Formulas

Automations

checkbox

Accepted answers

All comments

S.Stone

Would a solution like this work for you?

The column formula in "Check 3" will check the box if either of the first two columns are checked. It updates automatically with any changes to the first two columns.

=IF(OR([Check 1]@row = 1, [Check 2]@row = 1), 1, 0)

Cody Loftin

Unfortunately that won't work for me. I think maybe I wasn't clear enough in what I'm looking for.

The left column not editable because it has a formula. I want to be able to edit it. However, there is no way to do this in Smartsheet.

I have created a new column that is editable, and I just want it to mirror the left column whenever it changes. If the left column is checked, the right should be. If it isn't the right shouldn't be checked.

In my screenshot, the first row should be Unchecked, Unchecked. Second and third should be Checked, Checked.

JR90

Try this:

=IF([VPN*]@row = 1, 1, "")

The only thing with this is you cannot make it a column formula, because then it won't be editable. You would just have to copy the formula down for the rows you would need

Cody Loftin

That's not really what I'm looking for. Copying the formula all the way down leaves a ton of blank checkboxes, and when it copies new data into my sheet from another sheet, it will move that data even further down than it already does.

Is there really no way to use Automation to just copy a cell within my own spreadsheet? This feels like extremely basic functionality and it's crazy that this tool that's supposed to be helpful can't even do a basic thing like copying data one cell over when that cell is filled.

Adam Murphy

I think the problem with your automation is that you cannot use a formula field (vpn*) to trigger an automation. If you manually run that automation it should work because it's bypassing the trigger. One way you can workaround this by using a scheduled automation instead, but it's obviously not "real-time" in that case. Another option may be to change your logic to use automation to populate that vpn* field rather than a formula, just a thought.

Cody Loftin

Thanks Adam, that actually all makes sense now. I could just have it run on a time based thing, but even running it every day might be too little. Was hoping to force it to run twice a day at the least if that's the work around I end up going with, is there any way to force that automation to run more often or is it limited to that once a day?

JR90

@Cody Loftin try this, everytime the VPN* cell is checked it will check the VPN cell and its editable

Adam Murphy

@Cody Loftin, I usually just duplicate the automation rule and change the time when I want it to run more than once a day. Hope that helps!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.