Copy formula to the entire column

Is there an easy way to apply a formula to an entire column?

I am using this formula to calculate days between two assignments columns:

=NETWORKDAYS([Review Start Date]@row , [Review End Date]@row )

I am calculating the days on the next column. [Days Open]

I can click and drag the formula to the other cells in the column but I am trying figure out a way to automatically apply to each cell in the entire column when new cells are added.

I've applied the formula(copy and drag) to 3 cells under the first cell that has the formula, but the sheet is not auto applying the formula to the subsequent cells in the column (5-120).

Find more posts tagged with

Accepted answers

Andrée Starå

https://community.smartsheet.com/discussion/141759/copy-formula-to-the-entire-column

Hi,

I hope you're well and safe!

You can convert it to a Column Formula.

More info.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Did that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

Andrée Starå

https://community.smartsheet.com/discussion/comment/492369#Comment_492369

Yes, you can add something like this.

=IF([Review End Date]@row <> "", your original formula without =

Make sense?

Did it work/help?

✅ Remember! Did I help answer your questions or solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. It will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

All comments

Andrée Starå

https://community.smartsheet.com/discussion/141759/copy-formula-to-the-entire-column

Hi,

I hope you're well and safe!

You can convert it to a Column Formula.

More info.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Purnima Gore_PGPS

Hi @HarryS

Click in the formula and then right click and choose the convert to column formula option at the bottom of the list.

Thanks @Purnima Gore_PGPS

HarryS

https://community.smartsheet.com/discussion/comment/492313#Comment_492313

PERFECT!! I had no idea about the column formula. Worked perfectly! Thank you!

Andrée Starå

https://community.smartsheet.com/discussion/comment/492317#Comment_492317

Excellent!

Happy to help!

✅ Remember! Did I help answer your questions or solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. It will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

HarryS

One final question on this topic.

I have applied the formula to the column, but now all cells in the column that have no End Date populated now shows: #IINVALID DATA TYPE

Is there a way to clear the cells if there are no dates entered yet, or must this error message remain until the dates are populated?

Andrée Starå

https://community.smartsheet.com/discussion/comment/492369#Comment_492369

Yes, you can add something like this.

HarryS

https://community.smartsheet.com/discussion/comment/492394#Comment_492394

That worked! YOU ROCK! Thanks for the response!

Andrée Starå

https://community.smartsheet.com/discussion/comment/492486#Comment_492486

Excellent!

Happy to help!

✅ Remember! Did I help answer your questions or solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. It will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

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.