Duplicate sheet formulae in new sheet

[peanutaxis]

Hi,

- I have a Calc with four sheets. [Sheet 1] [Sheet 2] [Sheet 3] [Sheet 4].
- Some of the cells in Sheet 1 refer to cells in Sheet 4 (but are not absolute values).
- I want to duplicate the entire Sheet 1.
- If I copy the entire Sheet 1 and ask to insert this new Sheet 0 before Sheet 1, the copied cells that referred to Sheet 4 now refer to Sheet 3. So the
copied formulae are remembering the order of the sheets and moving the formulae along the sheets.

Here's the punch line. I can drag Sheet 4 to wherever I want; I can mix up the order of the sheets all I want and the formulae in Sheet 1 will still always refer to the CORRECT Sheet 4, no mater where it is in the order.

This is @#$%ing retarded. And it needs to stop.

[Alex1]

Insert a dollar sign in front of the sheet name in the formulas. Then they will remain unchanged when copied to another sheet.

[Lupp]

Any user of Calc must know the differences between absolute addresing and relative addressing for rows an columns. What's "absolutely ridiculous" if the same concept is applied to sheet addressing?

[Hagar Delest]

In LibreOffice, the $ is inserted by default for sheet reference.
I think it is the best compromise since by default, the reference is absolute and if for some reason it has to be relative, then the user still has the possibility to do that.
Having absolute reference for sheets makes sense: rows and columns are often appended/deleted/moved, with plenty of data used inside a sheet. Relative reference for sheets is quite rare.

You may be interested by: [Tutorial] Considering a Switch from OpenOffice to LibreOffice? Some Useful Information.

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the 🖉 button if your issue has been fixed.

[FJCC]

You can replace the relative references with absolute ones using Find & Replace. When you are in the Find & Replace dialog, check under More Options that the search is being done in Formulas.

[peanutaxis]

Insert a dollar sign in front of the sheet name in the formulas. Then they will remain unchanged when copied to another sheet.

No. Having to do this to every cell - even if I did so from the beginning of building up my sheet - is ridiculously onerous.

Any user of Calc must know the differences between absolute addresing and relative addressing for rows an columns. What's "absolutely ridiculous" if the same concept is applied to sheet addressing?

It's absolutely ridiculous because I imagine tens of thousand of people just want to copy a damn sheet and can't because of this. And that approximately zero people want to use relative sheet addressing. Never in my entire life have I wanted to do this.

[FJCC]

As mentioned earlier, you can use Find and Replace to place a $ before the sheet name in a reference. It will take a few seconds to do a whole sheet.

[peanutaxis]

In LibreOffice, the $ is inserted by default for sheet reference.
I think it is the best compromise since by default, the reference is absolute and if for some reason it has to be relative, then the user still has the possibility to do that....[/rel].

That's more sensible

As mentioned earlier, you can use Find and Replace to place a $ before the sheet name in a reference. It will take a few seconds to do a whole sheet.

Thanks!

[Lupp]

Any user of Calc must know the differences between absolute addresing and relative addressing for rows an columns. What's "absolutely ridiculous" if the same concept is applied to sheet addressing?

It's absolutely ridiculous because I imagine tens of thousand of people just want to copy a damn sheet and can't because of this. And that approximately zero people want to use relative sheet addressing. Never in my entire life have I wanted to do this.

My point is that the actual behaviour of LibO (as described by "Hagar Delest" in one of his posts) is inconsistent - and I appreciate consistency: If row addressing and column addressing both are relative by default, sheet addressing should also be.

Whether this behaviour is advanatgeous concerning efficiency depends on the use-case.
Since it's very simple to convert relative sheet references into absolute ones during the creation of a formula, we can leave it to the user to decide what they want. No user should feel disrespected when conscious decisions are required of them.

Simply omit claims to be member of a vast majority. Firstly you don't know, secondly majority shouldn't decide everything.
And, please, also omit offensive words.

[Alex1]

The easiest way is to select all cells refering to other sheets (or simply the whole sheet) and press 4 times Shift+F4!

[Hagar Delest]

My point is that the actual behaviour of LibO (as described by "Hagar Delest" in one of his posts) is inconsistent - and I appreciate consistency: If row addressing and column addressing both are relative by default, sheet addressing should also be.

I agree this is not fully consistent. However, as I said, sheets are not considered the same way rows and columns are by most users.
Consistency is not always the best criterion.

Whether this behaviour is advanatgeous concerning efficiency depends on the use-case.

Well, it seems that LO made a choice. If the OOo behavior was the default one, either LO changed that by purpose or there is no pressure from their user base to go back to the former behavior. That's what makes me think that most users don't want the relative behavior for sheets. I use spreadsheets a lot (especially at work with MS Excel) and I never ever wanted the sheets to be relatively linked when using formulas across sheets.

Since it's very simple to convert relative sheet references into absolute ones during the creation of a formula, we can leave it to the user to decide what they want. No user should feel disrespected when conscious decisions are required of them.

It works both ways.
The trick is that efficiency point of view, such consistency would please a very tiny number of users only. Thus leading to frustration (as seen in this discussion) and energy lost by the overwhelming number of users having to change their formulas.