[Solved] Referenced cell moves less than formula copied

Discuss the spreadsheet application

[Solved] Referenced cell moves less than formula copied

Postby RMcL » Tue Jan 07, 2020 10:30 pm

Have a spreadsheet in OO with multiple tabs keeping track of expenses and budget. Consolidation tab pulls data from other tabs for various expense categories (e.g. Irrigation, Lawn, Building) and displays the data by monthly columns.

Example:

Month/Category:   January      February     March       April     May
Irrigation $75 $78 $80 $65 $85
Lawn $90 $90 $90 $90 $90
Building $25 $30 $28 $32 $27

The tab in which I need help is a monthly report tab for which each month contains three columns, for example:

January Report:
Column 1 Description Column 2 Description Column 3 Formulas
Various things Irrigation Formula to consolidation tab for January irrigation
Lawn expense Formula to consolidation tab for January Lawn
Building expense Formula to consolidation tab for January Building

When I copy the report to the next three columns to the right, the formulas to the relative cells in the consolidation tab also move three columns to the right.

Back in the 1990s when using Lotus I knew how to write the formula so the monthly report cells would only advance one column on the consolidated tab when copied, but cannot find an example nor do I remember how to do it.

Suggestions?
Last edited by robleyd on Mon Jan 13, 2020 11:56 pm, edited 2 times in total.
Reason: Format for readability
OpenOffice 4.1 on Windows 10 Pro 64-bit
RMcL
 
Posts: 7
Joined: Tue Jan 07, 2020 9:56 pm

Re: Referenced cell moves less than formula copied

Postby MrProgrammer » Tue Jan 07, 2020 11:37 pm

Hi, and welcome to the forum.

RMcL wrote:Back in the 1990s when using Lotus I knew how to write the formula so the monthly report cells would only advance one column on the consolidated tab when copied, but cannot find an example nor do I remember how to do it.
Begin by reading section 8. Using formulas and cell references in Ten concepts that every Calc user should know. I don't understand your goal or your example. You may need to use OFFSET(). Needing to use OFFSET() suggests to me that you should redesign your spreadsheet. Using the wrong design means that you will have to fight with Calc to get the results you want.

RMcL wrote:When I copy the report to the next three columns to the right, the formulas to the relative cells in the consolidation tab also move three columns to the right.
1.Attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). I will not help if you attach a picture, though others might.
2. Tell us exactly which columns on which sheet you are moving and to where.
3. Confirm that you are copying them and not moving them.
4. Tell us what happens in your spreadsheet when you do this.
5. Tell us what you would like to happen instead. Which cell(s) should have which formula(s)?
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3962
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Referenced cell moves less than formula copied

Postby RMcL » Wed Jan 08, 2020 12:16 am

I've been using spreadsheets since the mid 1980s and understand relative and absolute addresses, if that's what you're suggesting I need to understand.

As I indicated, I copy the monthly report to create the new month's report - it ends up three columns to the right of the previous month (in columns 4, 5 and six in my example. Since the formulas are copied three columns to the right of the originals, the referenced cell reference changes three columns to the right. I want the change to be but one cell to the right, so the February report column containing the formulas references the single column where the February data is accumulated rather than April data (three columns to the right of the January data.

The spreadsheet is designed properly.
OpenOffice 4.1 on Windows 10 Pro 64-bit
RMcL
 
Posts: 7
Joined: Tue Jan 07, 2020 9:56 pm

Re: Referenced cell moves less than formula copied

Postby Villeroy » Wed Jan 08, 2020 12:39 am

It is not a known problem and nobody is able to reproduce it unless you attach some example document with dummy data. If you think it's a bug in the software, you may report the bug. In any case you need a document demonstrating the issue. However, OpenOffice is (almost) unmaintained since years. You may want to switch over to LibreOffice like many of the OpenOffice developers did several years ago.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Referenced cell moves less than formula copied

Postby RMcL » Wed Jan 08, 2020 5:32 am

This is not a problem with OO - it's working exactly as it is supposed to. If a cell with a formula is copied to another cell X number of columns or rows away, any reference to a cell in a formula in that copied cell is also moved X number of columns or rows from the previously referenced cell - relative addressing.

What I need is a work-around to limit the amount of relative movement. I'll create a sample spreadsheet with a table and a report so perhaps the issue I'm trying to solve is more readily understandable. I'm probably not using the correct terminology and my explanation and example are obviously unclear.
OpenOffice 4.1 on Windows 10 Pro 64-bit
RMcL
 
Posts: 7
Joined: Tue Jan 07, 2020 9:56 pm

Re: Referenced cell moves less than formula copied

Postby RusselB » Wed Jan 08, 2020 5:36 am

I think I understand what you are wanting, but am at a loss as to how to do it, without getting into some complex coding (C, Fortran, Cobol, as examples)... which I really don't want to get into.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Referenced cell moves less than formula copied

Postby RMcL » Wed Jan 08, 2020 5:52 pm

Attached is an OO spreadsheet I created to try to explain the issue in a simplified table and report on one sheet, rather than multiple sheets. Hope this helps. It's just a matter of relative cell addressing when copying and pasting, and figuring out a way to limit the relative movement in a formula or with a function.

I'm not looking for any special coding involving Fortran, etc. Just trying to determine whether this functionality is already available using a combo of built-in functions.

I have been making the changes needed to reference the correct cells by using find and replace for the part of the formula/function that refers to the incorrect column of cells. It works, but take a little time and there has to be a better way! If I didn't know that Lotus could handle this problem (back in the 90s) I wouldn't even be attempting to do it.
Attachments
Example problem for OO forum.ods
Example OO spreadsheet
(14.89 KiB) Downloaded 18 times
OpenOffice 4.1 on Windows 10 Pro 64-bit
RMcL
 
Posts: 7
Joined: Tue Jan 07, 2020 9:56 pm

Re: Referenced cell moves less than formula copied

Postby MrProgrammer » Wed Jan 08, 2020 8:14 pm

RMcL wrote:I'm not looking for any special coding involving Fortran, etc. Just trying to determine whether this functionality is already available using a combo of built-in functions.
HLOOKUP is an easy solution. Learn about it by reading [Tutorial] VLOOKUP questions and answers.
202001081200.ods
(16.13 KiB) Downloaded 17 times

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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3962
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Referenced cell moves less than formula copied

Postby RMcL » Wed Jan 08, 2020 10:14 pm

I wondered about that - have used it before in different circumstances. Thanks for the suggestion - I'll look into it.
OpenOffice 4.1 on Windows 10 Pro 64-bit
RMcL
 
Posts: 7
Joined: Tue Jan 07, 2020 9:56 pm

Re: Referenced cell moves less than formula copied

Postby RMcL » Mon Jan 13, 2020 8:08 pm

Sorry for the late reply. HLOOKUP works to do what I need. Thanks for the suggestion.
OpenOffice 4.1 on Windows 10 Pro 64-bit
RMcL
 
Posts: 7
Joined: Tue Jan 07, 2020 9:56 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 25 guests