Applying formula/conditional formatting to an entire column + hiding the column

JLC

Hi all, I am using the LEN formula in one column ("EN title character count") to show the number of characters in another (column "EN title") then applying conditional formatting to this column ("EN title") to show as bolded and red when the character count is greater than 80. This works perfectly, but I'd like to be able to hide the "EN title character count" column and shove it so the end of my sheet as we don't need to actually SEE the count, only see the bolded red title when the count is over 80. However, when I hide the column the formula doesn't seem to stick when I have new form submissions (which is the only way information is added to this sheet other than when I make manual changes). The formula worked in the new rows via form submissions when unhidden, but once that "EN title character count" column was hidden it no longer worked. Is there any way to apply my formula to the entire column so that this doesn't happen again? Let me know if I can clarify anything. Thank you!

JLC

Sorry, finally found another thread that addressed what could be going wrong. Another +1 for being able to apply a formula to the column level instead of cell level. Too many hands in the pot and I can't possibly ensure that EVERYONE is deleting "used" rows to keep the formula from breaking it's auto-fill. Thanks!

J. Craig Williams

Jaye,

 

Once the formula appears in every cell in the column, hide and lock the column.

The auto-fill should be OK after that, unless someone deletes ALL the rows. I hope that won't happen.

The lock won't keep an admin from changing it, but it will remind them to be careful.

 

Hope that helps.

 

Craig

JLC

Hi Craig! Thanks so much for the input. For my own understanding, how will locking the columns help with the formula auto-fill? Is it just a matter of it will auto-fill regardless of what cells have data entered/deleted, without those rows being fully deleted?

JLC

Hi Craig, I actually gave your suggest a try and now, no matter what, the formula won't stick to new rows even after deleting the "blank" rows under my last form entry. Columns with formulas are locked and hidden. Any tips?

Amy Johnson

Just to confirm... the two rows above the new row contain the exact same formula?

And you are adding a new row by inserting a row? And you add text to the row after inserting it?

JLC

Hi Amy, yes they do. We're adding new rows via web form, BUT then have to drag it into the appropriate parent (categorized by weeks) so that may be part of the issue. In any case, I don't think this will work for us longterm as we are constantly adding new parent headings to suit us when new weeks/months arrive.

J. Craig Williams

Jaye,

 

I am missing something.

Moving the row should not mess up your formula.

I assume the formula (now in a hidden, don't-touch-me column) references a different column on the same row. Does it?

 

Would it be possible to show a screen shot or share the sheet to me?

jcwill23@gmail.com

 

Craig

 

 

 

JLC

Hi Craig, I think the issue is how we work - we have everything broken down as parent/child rows by month then week (i.e. February > Feb 1 - 5, Feb 8 - 12, etc). Each time we approach a new week, we have to create another row for that upcoming week and that would require to drag the formula down to that line, wouldn't it? At least I've had the same issue in other sheets where I manually enter data into a row beneath one with a formula - I have to drag the formula down to extend to the new row.

J. Craig Williams

Jaye,

 

I almost have it.

I need to think about it some more, though.

 

A screen shot would be helpful.

Or you can send it here via a WebForm

 

https://app.smartsheet.com/b/form?EQBCT=cede1cfdee92474b9a065981a9fba84c

 

Craig

JLC

Hi Craig! What exactly do you need a screenshot of? Thanks a bunch for the help!

JLC

 

Hoping this is helpful. This web form submission just came in, just like this, without the formula filling down. Any idea? Do you need a better view of something?

JohnHinkle

I have noticed sometimes autofilling can get wonky on a sheet. Try making a copy of the sheet and see if the autofill works on the copy. 

J. Craig Williams

Jaye,

 

Apologies for the long delay in answering.

I was distracted and then a victim of the login issue. I'm a bit behind.

 

When I have seen problems with auto-fill, I delete all empty rows below my data, save the sheet, and the autofill returns.

But this is a rare occurence.

 

Craaig

JLC

Hi Craig, oh no! And yet you're still ruling the forums by helping everyone else. Kudos to you. I've tried your suggestion already over the past week or so but this doesn't work for us either for some reason. It works for the next incoming submission, we move that submission row up to the respective parent folder (based on week of the month), and then the next incoming submission no longer autofills!

J. Craig Williams

Oh, that's new information. Let me try that.

 

I'm still learning so much from the Community, both by trying to help and reading other posts. 

 

Craig