cardex formulas

[Lunat]

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 !!!

 

[Aladin Akyurek]

On 2002-10-26 12:57, Lunat wrote:
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 !!!

I assume that your dates are in dd/mm/yyyy format. Care to give the expected results in A5, B5, C5, and D5 in Sheet2?

Also: You say "In cell B5, it should give me all the IN figures on 01/10/2002 in the Day Shift." Does this mean a sum of all IN values on 01/10/2002?

 

[Lunat]

Yes the date format is DD/MM/YYYY

A5 should be 63
B5 should be 8
C5 should be 0 (Zero)
D5 should be 71


Yes B5 should have the sum of the IN values on 01/10/2002.

Basically, at any given day, when I enter the date in A1 and the shift in A2, it must given me the summary, that is op bal, in, out and cl bal.

 

[Aladin Akyurek]

On 2002-10-26 14:55, Lunat wrote:
Yes the date format is DD/MM/YYYY

A5 should be 63
B5 should be 8
C5 should be 0 (Zero)
D5 should be 71


Yes B5 should have the sum of the IN values on 01/10/2002.

Basically, at any given day, when I enter the date in A1 and the shift in A2, it must given me the summary, that is op bal, in, out and cl bal.

What follows shows your sample data. A5:E9 is named STable (from Shift Table) using the Name Box on the Formula Bar. I believe its definition should be dynamic, because I expect your data to change frequently.

aaCardexComputations Lunat.xls
	A	B	C	D	E
1
2	LightgreenareaisnamedStable
3
4	Date	SHIFT	IN	OUT	BALANCE
5	01-Oct-02	DayShift			63
6	01-Oct-02	DayShift	8		71
7	14-Oct-02	NightShift	3	2	72
8	16-Oct-02	DayShift		5	67
9	20-Oct-02	NightShift			67
10
Sheet1

The following shows the required computations given the conditions in A1:A2...

aaCardexComputations Lunat.xls
	A	B	C	D
1	01-Oct-02
2	DayShift
3
4	Openingbalance	IN	OUT	BALANCE
5	63	8	0	71
6
Sheet2

The formulas are...

In A5:

=INDEX(STable,MATCH(A1,INDEX(STable,0,1),0),5)

In B5:

=SUMPRODUCT((INDEX(STable,0,1)=A1)+0,INDEX(STable,0,3))

In C5:

=SUMPRODUCT((INDEX(STable,0,1)=A1)+0,INDEX(STable,0,4))

In D5:

=A5+B5-C5 [ as you suggested ]