D
deepika :excel help
I have something called WAS as sheet1 and FTp as sheet2. aLL i have to do is
automate FTP from WAS.
The entries in WAS are dynamic rows... So when I click on a macro i should
generate/populate the FTP sheet.
this is a weekly planning sheet.
The reason behind this is for each person to know their tasks one week in
advance.
So for a particular day, when summed vertically(manually) , a person shud
have max of 8 hours allocated against one/several tasks under his name.
In WAS thE inputs taken are ESTIMATION(starts from L6 to L* where * can be
any row number), RESOURCE(starts from F6), PRJ_NAME (column D from 6th row) ,
TASK(starts at E6), planned_ST (starts from H6) and planned_END(startrs from
I6
Now in FTP we have 5 weekdays in 5 columns from F2 to J2 as Mon, Tue,
Wed,Thu,Fri resp.
All columns in WAS start at row 6 and in FTP they start at row 3
Other FTP columns are RESOURCE (col A), PRJ_NAME (col D) , TASK (COL E)
I have to distribute ESTIMATION from WAS in the following manner:
first i have to take teh ESTIMATION and check for PLANNED_ST and PLANNED_END
date
and the status <> completed tehn
take proj_name , task, resource from WAS for taht particular status and put
it into appropriate columns n FTP(col names are given above).
teh columns F to J in FTP each should have a total of 8 hours only when
summed up vertivcally.
if estimation >40 in WAS for a new task , take only 40 and distribute it in
such a way that only 8 hours of work is planned for a day
if already sometime is planned say 7 hours, then this new task should be
planned only for 1 hour in taht day and macro should check the following days
whether there is bandwith to distribute.. like this many tasks may come.
estimation distribution should happen within the planned_st and planned_end
dates.. if teh bandwith is not available to distribute, tehn a warning should
be raised.
Now when we r at the end of a week, ACTUAL_EFFORT(col M) , ACTUAL_ST(col J)
adn ACTUA_END (col K) gets filled in manually in the sheet WAS.
SO for the distribution of tasks for next week, if teh task has ESTIMATION
of >40 then it will ck for teh ACTUAL_EFFORT , subtract the actual effort
with teh ESTIMATION and distribute the remaining across the days of teh week.
if the planned_st and planned_end date is within a week then well and good
the task will lastt for one week
if the planned_st and planned_end date span is more than 5 days then teh
distribution shud happen only for the one week(every friday this macro is run
so that teh tasks for next week is forecasted on friday eve itself)
automate FTP from WAS.
The entries in WAS are dynamic rows... So when I click on a macro i should
generate/populate the FTP sheet.
this is a weekly planning sheet.
The reason behind this is for each person to know their tasks one week in
advance.
So for a particular day, when summed vertically(manually) , a person shud
have max of 8 hours allocated against one/several tasks under his name.
In WAS thE inputs taken are ESTIMATION(starts from L6 to L* where * can be
any row number), RESOURCE(starts from F6), PRJ_NAME (column D from 6th row) ,
TASK(starts at E6), planned_ST (starts from H6) and planned_END(startrs from
I6
Now in FTP we have 5 weekdays in 5 columns from F2 to J2 as Mon, Tue,
Wed,Thu,Fri resp.
All columns in WAS start at row 6 and in FTP they start at row 3
Other FTP columns are RESOURCE (col A), PRJ_NAME (col D) , TASK (COL E)
I have to distribute ESTIMATION from WAS in the following manner:
first i have to take teh ESTIMATION and check for PLANNED_ST and PLANNED_END
date
and the status <> completed tehn
take proj_name , task, resource from WAS for taht particular status and put
it into appropriate columns n FTP(col names are given above).
teh columns F to J in FTP each should have a total of 8 hours only when
summed up vertivcally.
if estimation >40 in WAS for a new task , take only 40 and distribute it in
such a way that only 8 hours of work is planned for a day
if already sometime is planned say 7 hours, then this new task should be
planned only for 1 hour in taht day and macro should check the following days
whether there is bandwith to distribute.. like this many tasks may come.
estimation distribution should happen within the planned_st and planned_end
dates.. if teh bandwith is not available to distribute, tehn a warning should
be raised.
Now when we r at the end of a week, ACTUAL_EFFORT(col M) , ACTUAL_ST(col J)
adn ACTUA_END (col K) gets filled in manually in the sheet WAS.
SO for the distribution of tasks for next week, if teh task has ESTIMATION
of >40 then it will ck for teh ACTUAL_EFFORT , subtract the actual effort
with teh ESTIMATION and distribute the remaining across the days of teh week.
if the planned_st and planned_end date is within a week then well and good
the task will lastt for one week
if the planned_st and planned_end date span is more than 5 days then teh
distribution shud happen only for the one week(every friday this macro is run
so that teh tasks for next week is forecasted on friday eve itself)