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