PDA

View Full Version : Calculation based on cell reference



fusion
06-19-2011, 02:44 PM
Hi...I have an excel worksheet where summary financial data is based on periods and from this summary worksheet I want another detailed worksheet to be created with the results in detail based on the summary worksheet and another worksheet which is what the calculation will be based on.

For example on the summary worksheet I will enter cost data, description and the annual cost plus the start of from period of the cost.
Sp say if the annual cost is £12000 and the start from period is one then what I what is calculation on sheet 2 of the workbook where I can get the full expanded data. In this example I want 12 lines with the cost data, description and the cost to be £1000 in each on the 12 rows.

I have another worksheet which will be used for the calculation and lists all the start periods and how to base the calculation. In this sheet I have the start from period field, the period field and the ratio which will be used to work out the cost for each period.
The ratio will be based on the start from period. From the above example the start from period is going to be one so each of the 12 periods will have a ratio of one. But I could have the cost start from period 2 so I would want the ratio 0 in the first period and 2 in the second period and then 1 for the remainder 10 periods.
If the start from period was 3 then I would have the ratio as 0 for period 1 and 2 and then 3 for period 3 and 1 for the remainder periods etc.

The calculation will be annual cost/12 * ratio to give the monthly cost.

In the detailed sheet I want all the data from the summary sheet with the correct monthly cost for each of the rows and I also want to show the period from the calculation sheet.

I would like some guidance on how this can be achieved please.

Thanks

Bob Phillips
06-20-2011, 12:51 AM
Post the workbook, it will help a lot.

fusion
06-20-2011, 01:41 AM
Hi...please find find attached workbook which hopefully explains what I want to do.
In the first tab "EnterAnnualCost" the user will enter the summary annual cost.
In the second tab "Detailed", the VB code should split the costs over the months based on the third "Code" tab. The data should cope over from the first tab for each row and the period field should copy from the "Code" tab.
Hopefully the attached workbook will clarify what I am trying to achieve.
Please let me know if you need any further information.
Thanks