lease help with following “simple” cardex system on excel I’m trying to
develop:-
In sheet 1, I have the following info :-
A4 = Date
B4 = SHIFT
C4 = IN
D4 = OUT
E4 = BALANCE
A5 = 01/10/2002
A6 = 01/10/2002
A7 = 14/10/2002
A8 = 16/10/2002
A9 = 20/10/2002
B5 = Day Shift
B6 = Day Shift
B7 = Night Shift
B8 = Day Shift
B9 = Night Shift
C6 = 8
C9 = 3
D7 = 2
D8 = 5
E5 = 63
The cells E6, E7, E8 and E9 have the following formulas :-
E6 = (=E5+C6-D6)
E7 = (=E6+C7-D7)
E8 = (=E7+C8-D8)
E9 = (=E8+C9-D9)
Now Sheet 2 :-
It has the following Info:-
A1 = 01/10/2002
A2 = Day Shift
A4 = Opening balance
B4 = IN
C4 = OUT
D4 = BALANCE
I need the following formulas :-
i. In cell A5, after having entered the date in cell A1 and the shift in cell A2, it must give the opening balance at the end of the day shift on 01/10/2002 or the closing balance on 30/09/2002. If I enter in cell A1, 02/10/2002 and Night Shift in cell A2, it should give the opening balance as at 02/10/2002 or the closing balance as at 01/10/2002. ii. In cell B5, it should give me all the IN figures on 01/10/2002 in the Day Shift. iii. In Cell C5, it should give me all the OUT figures on 01/10/2002 in the Day Shift. iv. In Cell D5, it should be either =A5+B5-C5 or it should give me the closing balance on 01/10/2002 at the end of the Day Shift.
Basically, if I enter the date (in cell A1) and the shift (in cell A2) it should reflect on the cells B5:D5
Please help !!!
develop:-
In sheet 1, I have the following info :-
A4 = Date
B4 = SHIFT
C4 = IN
D4 = OUT
E4 = BALANCE
A5 = 01/10/2002
A6 = 01/10/2002
A7 = 14/10/2002
A8 = 16/10/2002
A9 = 20/10/2002
B5 = Day Shift
B6 = Day Shift
B7 = Night Shift
B8 = Day Shift
B9 = Night Shift
C6 = 8
C9 = 3
D7 = 2
D8 = 5
E5 = 63
The cells E6, E7, E8 and E9 have the following formulas :-
E6 = (=E5+C6-D6)
E7 = (=E6+C7-D7)
E8 = (=E7+C8-D8)
E9 = (=E8+C9-D9)
Now Sheet 2 :-
It has the following Info:-
A1 = 01/10/2002
A2 = Day Shift
A4 = Opening balance
B4 = IN
C4 = OUT
D4 = BALANCE
I need the following formulas :-
i. In cell A5, after having entered the date in cell A1 and the shift in cell A2, it must give the opening balance at the end of the day shift on 01/10/2002 or the closing balance on 30/09/2002. If I enter in cell A1, 02/10/2002 and Night Shift in cell A2, it should give the opening balance as at 02/10/2002 or the closing balance as at 01/10/2002. ii. In cell B5, it should give me all the IN figures on 01/10/2002 in the Day Shift. iii. In Cell C5, it should give me all the OUT figures on 01/10/2002 in the Day Shift. iv. In Cell D5, it should be either =A5+B5-C5 or it should give me the closing balance on 01/10/2002 at the end of the Day Shift.
Basically, if I enter the date (in cell A1) and the shift (in cell A2) it should reflect on the cells B5:D5
Please help !!!