# cardex formulas

#### Lunat

##### New Member
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

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

##### MrExcel MVP
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

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

##### New Member
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.

##### MrExcel MVP
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
ABCDE
1
2LightgreenareaisnamedStable
3
4DateSHIFTINOUTBALANCE
501-Oct-02DayShift63
601-Oct-02DayShift871
714-Oct-02NightShift3272
816-Oct-02DayShift567
920-Oct-02NightShift67
10
Sheet1

The following shows the required computations given the conditions in A1:A2...
aaCardexComputations Lunat.xls
ABCD
101-Oct-02
2DayShift
3
4OpeningbalanceINOUTBALANCE
5638071
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 ]

Replies
4
Views
323
Replies
14
Views
149
Replies
2
Views
155
Replies
2
Views
141
Replies
0
Views
132

1,191,584
Messages
5,987,492
Members
440,097
Latest member
Wint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back