Sheet Copy in Calc does not retain formatting/references

English
,
#1

I have built a template (.ots) with a worksheet named “Template”. The process is to sheet copy (right-click/move or copy sheet…/copy) to the end of the document with a new name - say NewSheet.

I have two problems with this:

  1. I have a column which in Template sheet which is a date format formatted [H]:mm. In the NewSheet it is formatted h:mm, which does not display the cumulative hours correctly.

  2. I have a cell with the following formula: =IF(ISNA(VLOOKUP(C5,Holidays.$B$3:$B$21,1,0)),"",VLOOKUP(C5,Holidays.$B$3:$C$21,2,0))
    The Holidays sheet is part of the spreadsheet.
    After copy I get:=IF(ISNA(VLOOKUP(C5,#REF!.$B$3:$B$21,1,0)),"",VLOOKUP(C5,#REF!.$B$3:$C$21,2,0))
    The cell display #REF and the reference to the Holidays sheet is lost.

If I format the formula with a static sheet reference:
=IF(ISNA(VLOOKUP(C5,$Holidays.$B$3:$B$21,1,0)),"",VLOOKUP(C5,$Holidays.$B$3:$C$21,2,0))
the actual sheet is incremeted to the adjacent sheet.

If I select and copy the 7 cells with this formula and paste into the new sheet it transfers properly.

IF I create a new sheet, then select-all/copy from Template in the the new sheet problem 1 goes away, but problem two still exists

Any insight to a solution would be much appreciated.

Paul

#2

Please name the version (and OS) you are using.
Was the document always saved to the native format of LibO Calc (.ods)?
Why do you use the comma as the parameter (“function”) separator. This aggravates global cooperation because the majority of locales can’t work with it. They need the semicolon in that place. The comma is used there as the decimal separator.
(See Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet? for details.)
Best: Provide an example document showing the issues for you.
Myself (and most users) never experienced this.

By the way: Relative sheet addressing will fail and cause a #REF! error in a copy of the sheet if it was copied to the left by tab-order too far.

#3

Lupp,

Thanks for the quick response. Sorry, forgot the version: 7.1.4.2 and OS: ubuntu20.04LTS

I originally created the document with LO and saved as .ods, but then saved as .ots

I used the comma when I switched from OO because it was required for compatibnility with Excel. If that has changed, then I will change (however it should be irrellevent for this problem?).

“By the way: Relative sheet addressing will fail and cause a #REF! error in a copy of the sheet if it was copied to the left by tab-order too far.” this appears to be what is happening, but is there a way to prevent this? what I am looking for is an exact copy of the sheet.

I would happily share the sheet. What is the best way to do that?

#4

Concerning relative sheet addressing the “exact” copy needs to adapt the name regarding the changed position. Only absolute addressing of sheets can be retained unadapted.
Users of many versions of Excel may expect every sheet reference to be absolute, because these Excels didn’t know relative addressing at all insofar. If you import a .xls document made with such a version by Calc, sheet addresses should be changed to absolute automatically.by prefixing the “$” sign.

I used the comma when I switched from OO because it was required for compatibnility with Excel.

Well. The hope for compatibility with MS junk will cause problems again and again. It is in vain, however, because a hostile commercial competitor always will re-enforce incompatibities. They “make their living” this way after all.

#5

Lull

Again I appreciate your quick response. I thought I was using absolute reference with Holidays.$B$3:$B$21 but it created the #REF result When I tried $Holidays.$B$3:$B$21 it actually treated it as relative and changed the sheet reference. Am I doing something wrong.

#6

Holidays.$B$3:$B$21 is referencing column and row the absolute way, but the sheet reference is relative!.
My remark concerning “many versions…” (I don’t know exactly which ones) of Excel explicitly was about sheet addressing.
You seem to misunderstand how spreadsheet software knowing relative sheet addressing handles it: The written reference uses the sheet’s name, but the sheet is identified as if referenced by its number. Only this way relative interpretation is possible.
$Holidays.$B$3:$B$21 would be required to get the sheet addressed the absolute way.

#7

Lupp

Excel became a red herring in our discussion as it was the reason for using comma in functions. I haven’t actually used excel since about 2012, but I was a power user and in company resource for it.

I was enamoured with OO and I have always been a proponent of open source software. I switched to LO many years ago when it came bundled with a new Ubuntu version.

Leaving that behind, as per my original note I did try the $ in front of the sheet name:
“ If I format the formula with a static sheet reference: =IF(ISNA(VLOOKUP(C5,$Holidays.$B$3:$B$21,1,0)),"",VLOOKUP(C5,$Holidays.$B$3:$C$21,2,0)) the actual sheet is incremeted to the adjacent sheet.”

Which is why I posted in the first place.

I have a workaround, which I don’t really like - I added the validation data into the Template sheet outside the defined print area. It works but to me it doesn’t make sense to copy all that data to every new sheet when a single instance would make more sense.

#8

If I understand correctly what you describe, there is a very strange problem with your specific LibO. I often copied sheets containing formulas using absolute sheet references. The referenced sheet never was “incremented” or decremented or otherwise adjusted then, but simply unchanged.
If you seriously can confirm the bad behavior (even with a document specifically created for the testiung), I would guess your user profile is corrupted.
Try with very simple formulas! (Omit the lookup, isna, and the like. A single cell reference should do. We don’t need to discuss the comma any more. You surely read what I linked to.)
Attach a .ods showing the bad behavior for you.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.