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]
