Ask Your Question

How do I copy and paste the formulas from multiple cells at the same time? [closed]

asked 2016-05-10 02:04:04 +0100

Anov229 gravatar image

I have a spreadsheet with 13 pages and I want all the individual formulas from sheet 2 (labeled "Jan") cells C42:C51 to be copied to the same sells in 11 other sheets, is there a shortcut for this? I've tried copying and pasting the formulas but it always changes them and screws me up.

This is the formula I have in Jan.C41 =IF(StartHere.J72=0,"",StartHere.J72), Jan C42 has the same formula but it's referencing StartHere.J73, and so on and so forth. If I highlight Jan.C42:C51 and click copy then highlight and paste only the formulas into the next sheet, Feb.C42:51, I changes all the formulas to =IF(#REF!.J72=0,"",#REF!.J72) for C42, =IF(#REF!.J73=0,"",#REF!.J73) for C43 and so on. What am I doing wrong? Is there a way to do this without manually entering the formula into each cell in each sheet?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-09 23:28:21.641800

2 Answers

Sort by » oldest newest most voted

answered 2016-05-10 10:46:34 +0100

pierre-yves samyn gravatar image


In formula =IF(StartHere.J72=0,"",StartHere.J72) the sheet name is relative references. If the sheet StartHere is for example in the last position in the spreadsheet, Calc adapts the formula trying to find a sheet located immediately after the sheet StartHere.

Solution: use absolute reference for the sheet name: =IF($StartHere.J72=0;"";$StartHere.J72)


edit flag offensive delete link more

answered 2016-07-30 12:08:27 +0100

Lupp gravatar image

updated 2016-07-30 12:20:27 +0100

Concerning the related question how to replace relative sheet addresses by absolute ones (or vice versa) you may refer to my answer in this thread.

(In addition an unsolicited advice:)
Do not split equally structured data into different sheets. To filter by date or to restrict sums by conditions is easy. To evaluate a report across multiple sheets is complicated. To maintain such Calc documents is complicated even more. To implement enhancements for such a group of spreadsheets may soon become a nightmare.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2016-05-10 02:04:04 +0100

Seen: 882 times

Last updated: Jul 30 '16