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.
HTH.
--
View this message in context:
http://www.nabble.com/Varying-sheet-specific-master-formula--tf3475042.html#a9703229
Sent from the openoffice - users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
