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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
