TomW wrote:
Hylton Conacher (ZR1HPC) wrote:
TerryJ wrote:
Hylton Conacher(ZR1HPC) wrote:
TerryJ wrote:
Hylton Conacher(ZR1HPC) wrote:
Hi,
I have an extensive spreadsheet which I use to monitor my ADSL
usage on
a monthly basis. Asides from a tab for each month I also have a
Master
tab and the tabs for the graphs I have drawn from the data. All the
monthly sheets have the same layout as the master tab so therefore
changing the master tab would allow me to have a single source to
copy
from so that I could paste in all the months tabs to reflect the new
data set-up/formula.
What I would like to be able to do is link all the monthly static
tab
details to my master sheet i.e. column headings and formulae but
this
does not seem possible, or rather is possible with
Paste..Special..Link
but then the actual data values, all being zero in my Master tab are
carried forward to wherever I have pasted the link and the data
in the
monthly tab is not used.
To try and get around this error I copied parts of the Master tab
and
pasted it onto the monthly tab. This also did not work as the cell
references in the formulae I had in the Master tab changed to
different
values when I pasted them.
If anybody wants the spreadsheet concerned or if I can upload it
to a
central bugs repository, please let me know where or what your email
address is.
Regards
Hylton
Using OpenSUSE 10.2 with KDE
I suggest that, instead of using the "master tab" as your
template, you
create a separate sheet as a template and copy that each time you
need a
new
sheet. It could be hidden when not required.
I'm confused as I am already doing this, or did I misunderstand?
Each new sheet is opened in a new tab ie I have 12 tabs, one for each
month. There is another tab('Master') which holds all the formula that
need to be addplied to each monthly range of data. and there are
additional tabs for the graphs I have drawn.
The problem is copying the cells from the 'Master' tab and pasting
them
into each month as the cell references in the formula change ie
with no
data the formula in the 'Master' tab and any of the monthly tabs is
different.
Regards
Hylton
My suggestion was to avoid the need to copy formulas from the
"master tab" -
your formulas would be in the template. I probably do not
understand what
you mean by "master tab" - perhaps it refers to a template sheet
rather than
a sheet which contains summaries of data on the other sheets?
Could you give an example of a formula and how it is changing when
you copy
it. So far, I cannot reproduce the problem. The formulas
=COUNTIF($D$1:$D$26;$D1) (absolute references) and
=SUM(OFFSET(A1;1;0;1;$A$1)) (mixed references) both copy without
change from
one sheet to another in my test document.
You may have to have the template in another document and use Insert
>Sheet
from file.
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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
I just did the same sheet manipulation with linked cells in OO 2.0 in
Ubuntu virtual machine and I get the same results as OO 2.1 in WinXP.
TomW
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
