Date function expanded
 

Hi,

I have a worksheet for students fees collections.

A1: Student Number
B1: Student Name
C1: Fees (amount) collected
D1: Fees for which month? (number 1604 for April 2016)

When fill Column A with the Student Number, Student Name in Column B is
getting updated by a vlookup formula. I also want Column D (fees for the
month) updated by increasing the previous highest value for the same
student by one. If the previous highest value for the same student in
column D is 1602 (February 2016), I want 1603 to appear in column D for
the current payment.

Any ideas?

TIA
Ahmed

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Date function expanded
 

Hi Ahmed,

Am Thu, 21 Apr 2016 13:09:35 +0530 schrieb ahmed:

When fill Column A with the Student Number, Student Name in Column B is
getting updated by a vlookup formula. I also want Column D (fees for the
month) updated by increasing the previous highest value for the same
student by one. If the previous highest value for the same student in
column D is 1602 (February 2016), I want 1603 to appear in column D for
the current payment.

if you have done the entries for the first month enter following formula
into the next row and modify the the range (at the moment the formula is
written for row3):
=MAX(IF($B$2:B2=B3,$D$2:D2))+1
Insert the formula with CTRL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Date function expanded
 

On 21/04/2016 01:22 PM, Claus Busch wrote:
Hi Ahmed,

Am Thu, 21 Apr 2016 13:09:35 +0530 schrieb ahmed:

When fill Column A with the Student Number, Student Name in Column B is
getting updated by a vlookup formula. I also want Column D (fees for the
month) updated by increasing the previous highest value for the same
student by one. If the previous highest value for the same student in
column D is 1602 (February 2016), I want 1603 to appear in column D for
the current payment.

if you have done the entries for the first month enter following formula
into the next row and modify the the range (at the moment the formula is
written for row3):
=MAX(IF($B$2:B2=B3,$D$2:D2))+1
Insert the formula with CTRL+Shift+Enter


Regards
Claus B.


Excellent, thank you Claus. I need some more help from you.

Monthly fees are entered in column E. While most of the students pay in
full, some make part payment or for the following month also in advance.
In those cases when monthly payment is not equal to 3000, I want this
formula result to appear in a different color.

TIA.
Kind regards,
Ahmed.


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Date function expanded
 

Hi Ahmed,

Am Fri, 22 Apr 2016 06:16:24 +0530 schrieb ahmed:

Monthly fees are entered in column E. While most of the students pay in
full, some make part payment or for the following month also in advance.
In those cases when monthly payment is not equal to 3000, I want this
formula result to appear in a different color.

look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Student_Fees".
You also have to change the formula for the month because it doesn't
work correct when year changes.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Date function expanded
 

On 22/04/2016 01:05 PM, Claus Busch wrote:
Hi Ahmed,

Am Fri, 22 Apr 2016 06:16:24 +0530 schrieb ahmed:

Monthly fees are entered in column E. While most of the students pay in
full, some make part payment or for the following month also in advance.
In those cases when monthly payment is not equal to 3000, I want this
formula result to appear in a different color.

look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Student_Fees".
You also have to change the formula for the month because it doesn't
work correct when year changes.


Regards
Claus B.


Thank you, Claus.

I appreciate your effort to answer my question in details. I also got an
added bonus of finding out the balance outstanding. Your worksheet is
very neat and descriptive.

Let me try to understand the formulas in columns C & D. If I have any
difficulties, I will come back to you.

Thank you for your time again.

Kind regards,
Ahmed.

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Date function expanded
 

On 23/04/2016 06:37 AM, ahmed wrote:
On 22/04/2016 01:05 PM, Claus Busch wrote:
Hi Ahmed,

Am Fri, 22 Apr 2016 06:16:24 +0530 schrieb ahmed:

Monthly fees are entered in column E. While most of the students pay in
full, some make part payment or for the following month also in advance.
In those cases when monthly payment is not equal to 3000, I want this
formula result to appear in a different color.

look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm

for "Student_Fees".
You also have to change the formula for the month because it doesn't
work correct when year changes.


Regards
Claus B.


Thank you, Claus.

I appreciate your effort to answer my question in details. I also got an
added bonus of finding out the balance outstanding. Your worksheet is
very neat and descriptive.

Let me try to understand the formulas in columns C & D. If I have any
difficulties, I will come back to you.

Thank you for your time again.

Kind regards,
Ahmed.

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


Yes, I understood everything.

Thank you so much.

Kind regards,
Ahmed.


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus