[Solved] Referenced cell moves less than formula copied

[RMcL]

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?

[MrProgrammer]

Hi, and welcome to the forum.

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.

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)?

[RMcL]

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.

[Villeroy]

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.

[RMcL]

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.

[RusselB]

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.

[RMcL]

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.

[MrProgrammer]

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 80 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.

[RMcL]

I wondered about that - have used it before in different circumstances. Thanks for the suggestion - I'll look into it.

[RMcL]

Sorry for the late reply. HLOOKUP works to do what I need. Thanks for the suggestion.