Keeping formulas when pasting rows of data

samgiese

I',m in a bit of a pickle with one of our sheets. So right now I've got a sheet that is being filled in automatically with a script that I made. It is taking a CSV that is constantly growing, converting it into an excel doc, and then copying & pasting the entire thing into a smartsheet. This works great for the most part, except for a column that utilized a formula that needs to be dragged down every time new data is pasted.

Is there a workaround for this issue? Maybe a different approach of formula?

Right now the TOTAL Cat column is counting how many cells in the row have an x in them to the left (basically a checkbox cell) & the Count of REG box is seeing how many rows total actually have one or more x in them. I figured out a way around the TOTAL Cat formula, but am stuck on the Count of REG & it's total up top. Can anyone help me find a way around this please?

Thank you!

Paul Newcome

What is the purpose of the [Count of REG] column? Are you just flagging a 1 for each row where [TOTAL Cat] is greater than zero?

samgiese

Hey Paul,

Yep that's exactly what's going on. I found a way to count the TOTAL Cat without the dragdown formula, but can't find a way to then calculate the Count of REG.

Paul Newcome

You could do away with the column entirely and just use

=COUNTIFS([TOTAL Cat]:[TOTAL Cat], @cell > 0)

Genevieve P.

Hi @samgiese

This week we released a feature called Column Formulas, where you can set one formula for the entire column. It sounds like this feature may resolve your initial issue so I wanted to make sure you heard about it!

Keep in mind you would need to use @row instead of row references (ex. [TOTAL cat]@row instead of [TOTAL cat]2).

See our Help Center for more information: https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Cheers,

Genevieve