Effort Hours Tracker - Planned Vs Actual

[MaheshK5277]

Dear All,

Need help to automate a excel tracker for Effort Hours (Planned Vs Actual). Request you to see the attachment.

Brief:
I have a Project plan for which i have Resource role in Col A, FTE allocated in Col B, Hrs/Week(45 for FTE 1, which is 100% loading) in Col C, & then Effort in Weeks in Col D (this is the planned figure), followed by effort hours in Col E (which comes as a result of Col B x Col C x Col D). I need a formula so that the columns F to Z denoting Weeks (like Wk 1,2,3 & so on..) can automatically populate with the number of hours for estimated weeks planned.

Also, i need a second condition to be met, when the FTE figure in Col B is anything different than 1, e.g. 0.5, 0.75; then the standard hours/week which is 45 for FTE as 1, should calculate the effort hours as per FTE allocated, i.e. 0.5 FTE would mean that the standard working hours per week would be 50% of 45, i.e. 22.5.

Any help is much appreciated...i dont know whether this can be achieved, but still giving it a try. Thanks in advance

[AliGW]

In E13 copied across and down:

=IF(COUNT($E13:E13)<=$D13,$C13*$B13,"")

Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
12	FTE	Hrs/Week	Effort in Weeks	Effort Hours	Wk-1	Wk-2	Wk-3	Wk-4	Wk-5	Wk-6	Wk-7	Wk-8	Wk-9	Wk-10	Wk-11	Wk-12	Wk-13	Wk-14	Wk-15	Wk-16	Wk-17	Wk-18	Wk-19	Wk-20	Wk-21
13	1	45	2	90	45	45
14	1	45	19	855	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45
15	1	45	21	945	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45
16	0.5	45	21	472.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5
17	0.5	45	21	472.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5	22.5

Sheet: Budget-Estimation-Plan Vs Act

[MaheshK5277]

Dear AliGW,

This is fantastic !! Thank you. It helps me a great deal to save my time & effort. Appreciate your quick support.

I am marking this as SOLVED.

[AliGW]

Great - glad to have helped.