Ask Your Question
0

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

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

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 close merge delete

2 Answers

Sort by » oldest newest most voted
1

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

pierre-yves samyn gravatar image

Hi

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)

Regards

edit flag offensive delete link more
0

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

Lupp gravatar image

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

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 740 times

Last updated: Jul 30 '16