Maintaining references to a cell when its contents are moved

Discuss the spreadsheet application

Maintaining references to a cell when its contents are moved

Postby KnowThyself » Thu Mar 14, 2019 11:44 pm

I have a large spreadsheet which I use for calculating prices for renting an apartment. The prices change from high to low season and from year to year. I have on one worksheet a group of four columns which contain significant dates for current year (leftmost column) and the three succeeding years; I'll call these the black group. Just to the right of these are another set of twelve columns which contain similar data for the current year and several succeeding years; I'll call these the blue group. The black group gets its data from the blue group, Column C from Column N, Column F from Column P and so-on. (I am aware that the correspondence is not regular.) I want to write a macro which can be used for annual updating. The leftmost blue column will be cut and moved to an archive worksheet. Column P will then be cut and pasted into Column N, Column R will be cut and pasted into Column P etc so that the entire blue group moves to the left. I have written the cell contents in the black group to read the cells in the blue group: C2 reads N2, F2 reads P2 for example. Also cells in the black group do a bit of simple arithmetic and produce extra dates, which is the reason for having two groups. My problem is that when the blue dates are moved, the clever black cells do not maintain the cell references which I have given them, but chase the data in the blue cells to their new addresses. Absolute addressing does not provide a solution, and neither does named addressing. How can I persuade cell F2 to keep its content as P2 instead of changing it to N2?
With thanks.
I use: Open Office 4.1.6 and macOS High Sierra 10.13.6
KnowThyself
 
Posts: 1
Joined: Thu Mar 14, 2019 11:13 pm

Re: Maintaining references to a cell when its contents are m

Postby MrProgrammer » Fri Mar 15, 2019 12:40 am

Hi, and welcome to the forum.

KnowThyself wrote:How can I persuade cell F2 to keep its content as P2 instead of changing it to N2?
[Tutorial] Formula Adjustments during Copy and Move
Use Copy/Paste instead of Cut/Paste. If you want the source cells to be empty, clear them after the content is copied. Copy/Paste does not update dependent references.

KnowThyself wrote:neither does named addressing
If using Copy/Paste causes other difficulty for you and you must use Cut instead of Copy, use relative defined names, as discussed in the tutorial. Or use INDIRECT. =P2 is updated when P2 is moved. =INDIRECT("P2") is not updated when P2 is moved.

KnowThyself wrote:I want to write a macro …
If none of those ideas helps, have your macro save the content of F2, perform the move, and then restore the content of F2. It's your macro and you're the programmer. You can do as you please. See http://www.pitonyak.org/oo.php.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3840
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: robleyd and 33 guests