Hylton Conacher(ZR1HPC) wrote:
>
> TerryJ wrote:
>>
>>
>> Hylton Conacher(ZR1HPC) wrote:
>>> Hi,
>>>
>>> I have a complex spreadsheet system to calculate my expected ADSL usage
>>> based on my previous usage.
>>>
>>> The speasheet has a master sheet, 12 monthly sheets, a total sheet, and
>>> a number of sheets that contain graphs.
>>>
>>> The formulae on each month relate to data on that monthly sheet and the
>>> formulae do not change much ie all the formula will depend on the number
>>> of days in the month ie 28,29, 30, 31 but there basic structure will
>>> stay the same. Each formula is pulling information from a particular
>>> cell on the Totals sheet, per month, and then further calculating on it
>>> using the dat on the current month sheet ie All the formulae in January
>>> pull data from $totals.B1 and then further calculate it using
>>> information on Jan sheet. ie cont..All the formuale in February pull
>>> data from $totals.B2 and then further calculate it using information on
>>> the Feb sheet.
>>>
>>> What I would like to do is have a Master sheet which contains all the
>>> formulae (31 rows in total), that will allow for the linked formulae to
>>> use the sheets data they are pasted onto e If I need to use the
>>> formulae in another month then I would like to link the correct number
>>> of cells, as per the number of days in the month, from the Master sheet
>>> to the new month.
>>> This way if there is a small change in the formula, I can change it on a
>>> single sheet and that same change will be reflected on all of the 12
>>> monthly sheets.
>>>
>>> This is however where the SNAFU comes in. I only want the formulae
>>> pasted BUT I want to use the data on the sheet tey were pasted onto, not
>>> the data from the Master sheet, which are all blank.
>>>
>>> I have thought about creating a HUGE master sheet with all the months
>>> formulae listed but this does not help.
>>>
>>> The formulae are still using the data values from the Master sheet and
>>> not from the month onto which the formulae were posted.
>>>
>>> I was thinking of a formula syntax that will change as it is pasted onto
>>> each sheet ie on the Jan sheet it might be =Sum($Totals-<month name>.d2)
>>> where <month name would be Jan. For the Feb sheet the formula would be
>>> =Sum($Totals-<month name>.d2) where <month name> would be Feb.
>>>
>>> Any ideas. If you need to get an idea of the spreadsheet, please ask for
>>> it and I will mail it to you.
>>>
>>> Regards
>>> Hylton
>>>
>>>
>>
>> I take it that what you call the "master sheet" is a template. I suggest
>> you have a look at the INDIRECT function. That would enable you to have
>> the
>> name of the month in a set cell in each month's sheet - say A1. Your
>> formula (obviously you need to work out the details) would be something
>> like:
>> A1 contains February. B1 contains =INDIRECT("$Totals." & A1 & "D2") -
>> you
>> do not need to use SUM for a single cell.
>>
>> Help contains an example if you need to use SUM with INDIRECT.
>>
>> Perhaps you need to reorganise the document. I would have thought the
>> Totals sheet should contain a summary of the contents of the various
>> monthly
>> sheets. Still, I don't have the full picture and don't necessarily want
>> it.
>
> Wow that INDIRECT function is a SERIOUS mother of a formula and I have
> to admit, even after reading teh help 4 times, I was still confused :)
>
> If no one else has any ideas, I have decided to put up with changing the
> cell formula instead of linking them. The $ you mentioned, solved most
> problems, and the rest can seemingly only be sorted out via checking
> each sheet individually and adapting the formula therein as there re too
> many variables in the formulae.
>
> Tnx
> Hylton
>
>
You can't give up that easily. I have problems following Help and HowTos.
If you ever try Linux, you will discover one of the most frustrating
experiences known to humankind is trying to make sense of "man" pages.
I find the best way to work out how to use something is to start trying.
This is a simple example:
In Sheet1.A1 type some data. The remaining entries are on Sheet2 and are
designed to get the data in Sheet1.A1 without having to use the Sheet name
or, for that matter, the cell address in the formula which gets the data in
the first cell of the document.
First, in Sheet2.A1 enter Sheet1.A1. In Sheet2.B1 enter =INDIRECT(A1).
>From that you will get the data in Sheet1.A1.
Now in Sheet2.A2 type Sheet1. In B2 type A1. In C2 enter =INDIRECT(A2 &
B2). You get the data in Sheet 1.A1.
A variation of the last: in D2 type =INDIRECT(B1 & "A1") [the equivalent of
=INDIRECT(Sheet1.A1)]
Having said that, I should belatedly ask about your example
=Sum($Totals-<month name>.d2). Is the <month name> part of a sheet name and
why use SUM for what seems to be a single cell?
--
View this message in context:
http://www.nabble.com/Varying-sheet-specific-master-formula--tf3475042.html#a9726007
Sent from the openoffice - users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
