PDA

View Full Version : Link and Calculate



hyerim
01-20-2008, 08:41 PM
Hi,

i try to develop training budget system using excel.

In sheet 1, i have the training schedule as below:

Course NamePerson 1Person 2Person 31Course 1 20082Course 220083Course 32008

In Sheet 2, i hv budget summary:

Actual Budget200,000.00Budget Utilised to date0.00Balance200,000.00

What i want to do is, when i assign date in the schedule as below,i want to automatic calculate the course fee in the summary sheet. Let say, fee for course 1 is $1,500 and course 3 is $ 1,000,so when i enter the date for the course, the summary sheet will be like sheet 2:

Sheet 1
Course NamePerson 1Person 2Person 31Course 1 13-14 Mar 082Course 220083Course 312-14 Feb 08

Sheet 2
Actual Budget200,000.00Budget Utilised to date2,500.00Balance197,500.00

Can anybody help me with the formula to generate this?

anandbohra
01-20-2008, 11:47 PM
to be very frank no one understand cleary at one go what u want to do??

as it seems even u r not sure what exactly u want.


In sheet 1, i have the training schedule as below:

Course NamePerson 1Person 2Person 31Course 1 20082Course 220083Course 32008

In Sheet 2, i hv budget summary:

Actual Budget200,000.00Budget Utilised to date0.00Balance200,000.00

What i want to do is, when i assign date in the schedule as below,i want to automatic calculate the course fee in the summary sheet. Let say, fee for course 1 is $1,500 and course 3 is $ 1,000,so when i enter the date for the course, the summary sheet will be like sheet 2:

Sheet 1
Course NamePerson 1Person 2Person 31Course 1 13-14 Mar 082Course 220083Course 312-14 Feb 08

Sheet 2
Actual Budget200,000.00Budget Utilised to date2,500.00Balance197,500.00

the above sentence makes someone confused instead of throwing light.

pl give us one clear example with calculation to let us proceed with the formula.
to give u hint this can be done thorugh countif, sumproduct,vlookup either induvidually or with their combination

definately u get the required solution provided u r clear in presenting your problem

hyerim
01-21-2008, 07:17 AM
thanks for ur comment..sorry for the confiusion...let me explain again my inquiry..

i hv 2 sheets now..1st sheet is for my training schedule n another sheet is summary of the training budget which will calculate the fee of the course that i planned in my training schedule..

let say...in my training schedule, i put a date in one cell...once i put a date in that cell, i want the 2nd sheet to automatically read the fee of the course and calculate the balance of my training budget...so..everytime i put a date, a cell in 2nd sheet will automatically sum up all the fee for the course that i planned, n i can see the balance of my budget.

i hv attached my worksheet...hope it can help u all to understand my inquiry..thanks

anandbohra
01-21-2008, 10:58 PM
whatever I understood framed it into excel sheet u pl refer it & if not found acceptable revert back to me.