cardex formulas

Lunat

New Member
Joined
Oct 25, 2002
Messages
2
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 !!!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 25, 2002
Messages
2
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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 ]
 

Forum statistics

Threads
1,144,059
Messages
5,722,272
Members
422,419
Latest member
Havok390

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top