Challenge Copying and Pasting Formulas

Joseph DePalo

I am looking for some help copying and pasting formulas across sheets.

We are working to implement an automated supplier management dashboard which requires that 13 data points are generated for every supplier across all of our project sheets. With all the suppliers, that means a lot of rows and formulas. We then need to compile across sheets and manipulate the data, so they cannot be in a sheet summary. Column titles and formulas are set up to be the exact same across all sheets in question. I have set up all of the formulas within the template that we use to generate the project and I can confirm that they are correct and functioning properly.

When I go to copy the formulas into the existing project sheets, only the value is applied, and the formula will not carry over across sheets.

There are ~550 rows that need to be applied to about 20 sheets, so doing these manually for every cell is really not an option.

Maybe I am missing something super basic, but how to I copy multiple formulas from one project sheet into another? (I know I will hit the 500 row limit, I can deal with that)

Andrée Starå

Hi Joseph,

You’ll have to stay in the same browser tab when you go back and forth between the sheets and then it should work.

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

You can double click into the cell as if you are going to edit the formula, then highlight the whole thing and copy that way.

Cimafranca

@Andrée Starå and @Paul Newcome

I too am trying to reuse a lengthy formula among many sheets, but copy and pasting does not work. It only returns the value from the original cell; it doesn't adjust to the current destination cell. Any other tips?

Ray Lindstrom

@Cimafranca

Right-clicking a cell and selecting Copy, or single left clicking a cell and using Ctrl+C will result in the copying of the value listed in the cell as opposed to the formula.

If you're not seeing a cursor, you're only going to copy the value.

If you follow @Paul Newcome's advice from above, you should have no problem.

Double-click the cell that contains the formula, or if it's a column formula then right-click a cell in the column and select "Edit column formula". Then you will see the cursor, and can select the formula text that you want to copy.

Cimafranca

@Ray Lindstrom - thanks for the tip. Actually, I guess I wasn't clicking and selecting properly; I was merely pasting the value from original cell.

A sloppy work around solution: I copied and pasted the formula into Powerpoint (of all things), and then have repeatedly copy & pasted (Ctrl + V) from ppt into my destination Smartsheet cell.

DavidB

Although an older post, not all the answers presented here addressed the originally stated use case. I'll try to add clarity…

If in sheet A you have a grid full of formulas, and you wish to copy all those formulas to an identically laid out sheet B, then following Andre's example seems to work. It's partially practical if you will be selecting an entire matrix of cells from sheet A, hit Copy (Ctrl+C), then from the same browser tab navigate back to your other sheet B and open it, then paste based on the top left anchor point. When I tried Andre's suggestion, it worked. The key is using one browser window from which you navigate.

The other suggestions of copying the formula from within one cell is not applicable to the originally stated use case. But if you have a formula here or there across sheets you are wanting to copy—and/or have different sheets opened in different browser tabs—then copying the contents of the cell (the formula) will successfully paste into the contents of one other cell in another sheet opened in another browser tab.