TerryJ wrote:
TomW-3 wrote:
Hylton Conacher (ZR1HPC) wrote:
TerryJ wrote:
Hylton Conacher(ZR1HPC) wrote:
Hi Terry,
I am Cc'ing you the spreadsheet file so that you can see wheat I mean.
As a test copy the cell range of E3:G35 from the Master tab and paste
them into a forthcoming month sheet in exactly the same place ie
starting at E3.
The formula for E3 as per the master is =SUM((Totals.B5-D2))/(31-0) yet
the formula in E3 in April Month is =SUM(('Monthly usage'.B5-D2))/(31-0)
See how a cell reference has changed which I do not want/need to happen.
ie Totals' has become 'Monthly usage'.
I am happy to change the Master tab each month, copy the formulae and
then work in the monthly sheet. By doing this I cannot link the cells to
the Master tab as it changes once a month. ie B4 becomes B5 when we move
from March to April.
Further input?
Hylton
Using the workbook you attached on your 3/20/07 6:24am e-mail. (Date
received in Thunderbird)
Calc does behave very strangely when pasting cells with links to other
worksheets. It is not keeping track of links correctly. I tried a
number of different scenarios with copying cells with links and
duplicating worksheets.
If you duplicate a worksheet, the links will not be correct, unless the
worksheet is duplicated just to the right of the parent worksheet. Any
other location to the right of the 'parent' will give #REF! error.
If you duplicated the worksheet to the left of the 'parent', the linked
cell reference will be to the worksheet to the left of the last
worksheet, in this case 'Mar'. This progression of the link reference
will continue to shift left for any duplication to the left of the
'parent' (If you duplicate the worksheet 5 worksheets to the left of the
'parent', the linked cell reference will be shifted the same amount.)
If you try to 'copy'/'paste from the parent to the 'copied' worksheet,
the pasted cell links will reference the next worksheet beyond the
'Totals' tab.
If you paste to any other worksheet, I get the #REF! error.
Once you duplicate a worksheet you can move it around in the workbook
with no reference issues as long as you do not 'paste' any cells that
reference the 'Totals' worksheet.
I have not tried to duplicate these issues in any other workbook to see
if it is related only to this particular workbook or my OO application.
TomW
WinXP SP2 OO 2.1
Calc is behaving normally and appropriately. This requires a simple
amendment of the formulas. The sheet references in the formulas should be
absolute, not relative.
I find this behavior illogical. Maybe it is my MS background with
Excel. I would expect that when I link a cell to another worksheet,
that the reference would be to that sheet and not to its tab position in
the workbook. Gnumeric works as Excel. The link references the
worksheet itself. I never purchased the trial version of Quattro Pro
that came with my Dell, so I can see what its behavior would be.
I use Excel at work and only use OO at home for simple spreadsheets. But
I have been using this mailing list to see what others are having
problems with and what I would do to correct or explain the issue.
Hence my occasional replies.
And, Calc does come in handy for correcting corrupt or malfunction Excel
workbooks: removing the bloat that builds up from repeated use.
TomW
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
