Copying columns and formulas from master sheet to daily updated sheet

Theresa Bruns

I update my pipeline sheet daily from another source. I then add 16 columns and their 16 formulas to the updated sheet so I can manipulate the information for the reports on my dashboard. I do all of it manually daily because I have read that there is no way to copy columns with their formulas from one sheet to another. It seems to me there must be a simpler way and that I am missing something. Any help on this would be greatly appreciated. How can I easily move my 16 columns with their formulas to an updated sheet without retyping each formula manually daily? Thanks!

Andrée Starå

Hi @Theresa Bruns

I hope you're well and safe!

Unfortunately, it's not possible now, but it's an excellent idea!

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Here's a possible workaround or workarounds

• Add or update a so-called helper sheet and add a unique character in front of the formula. Something like this. °=YOUR FORMULA, and then you'd use Find and Replace to Replace the ° with nothing, which would activate the formula.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

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!

Dale Murphy

@Theresa Bruns Why does the pipeline sheet need to change every day? Can you not add data to the existing sheet, which already has your columns with formulae?

dm

Theresa Bruns

During our busy season, I download it daily from SF and then create a sheet within my workspace by importing the spreadsheet. When I have done that, it erases what is already in the sheet. Does that makes sense?

Dale Murphy

@Theresa Bruns I run a similar (sounding) process that gathers information from our ERP. My process is:

1. get file from ERP in Excel format
2. copy/paste that data (which is always structured the same) into an existing SmartSheet (this is a staging sheet, used only to validate data quality)
3. review results (just to make sure the data is clean)
4. copy rows into the production sheet, which has several formulae that add information to the incoming data (for e.g., store name is added to store number by cross referencing an address sheet). **This step is automated; only rows that are not classified as duplicates are moved.

Step 2 and 3 could be eliminated if you are good with the quality of data (the process is with end users who need to check things).

The production sheet has around 40 columns, while the staging sheet only has about 25. This process allows us to build a dashboard and link DynamicView and other reports to the production data. The users never have to go looking for the new data - it always shows up in the same place.

I prefer copying the data to the import functions based on the advantages gained.

Make sense?

dm

Theresa Bruns

@Dale Murphy @Andrée Starå You are both brilliant! Both solutions worked beautifully. Thanks!

Andrée Starå

@Theresa Bruns

Excellent!

You're more than welcome, and thanks for the compliment!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!