[Solved] Stop Auto-Increment when Copy/Pasting Formulas

Discuss the spreadsheet application
Post Reply
Jeffrey5
Posts: 2
Joined: Sat Aug 14, 2010 6:42 pm

[Solved] Stop Auto-Increment when Copy/Pasting Formulas

Post by Jeffrey5 »

Anyone know how I can stop the program from assuming I want different data when I copy paste? For instance, these are the formulas in Column A:

Code: Select all

='August 2010'.B40
='August 2010'.C40
='August 2010'.D40
='August 2010'.E40
='August 2010'.F40
='August 2010'.G40
='August 2010'.H40
='August 2010'.I40
='August 2010'.J40
='August 2010'.K40
='August 2010'.L40
='August 2010'.L40
When I copy and paste into column B they turn into this, so the are all off by a letter:

Code: Select all

='August 2010'.C40
='August 2010'.D40
='August 2010'.E40
='August 2010'.F40
='August 2010'.G40
='August 2010'.H40
='August 2010'.I40
='August 2010'.J40
='August 2010'.K40
='August 2010'.L40
='August 2010'.M40
='August 2010'.M40
I want it to end up with same ."B40", etc. as the first one.

Any ideas?

Thank you.
Last edited by Hagar Delest on Sun Aug 29, 2010 4:14 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.2 on Windows 7x64
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: How to Stop Auto-Increment when Copy/Pasting Formulas

Post by acknak »

Calc always tries to adjust the references when you move/copy a formula. If that's not appropriate, you have to write the references in a way that Calc will know they should not be adjusted: by including a "$" before each part of the formula that should remain constant.

So your first formula should look like this: ='August 2010'.$B40

For the other formulas, since you're apparently transposing a row-series into a column, you'll have to edit each one, or use Find & Replace to fix the others...

Or, select the cells with the data, copy, then--back in column B where you want the formulas--use Edit > Paste Special and tick the "Transpose" and "Link" options, then OK.
AOO4/LO5 • Linux • Fedora 23
Jeffrey5
Posts: 2
Joined: Sat Aug 14, 2010 6:42 pm

Re: How to Stop Auto-Increment when Copy/Pasting Formulas

Post by Jeffrey5 »

Thank you acknak, that worked, I changed it to this:

Code: Select all

='August 2010'.$B40
='August 2010'.$C40
='August 2010'.$D40
='August 2010'.$E40
='August 2010'.$F40
='August 2010'.$G40
='August 2010'.$H40
='August 2010'.$I40
='August 2010'.$J40
='August 2010'.$K40
='August 2010'.$L40
='August 2010'.$M40
And now it's working.

Thanks again!
OpenOffice 3.2 on Windows 7x64
Post Reply