Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
toolbar not expanded beverlydawn Excel Discussion (Misc queries) 1 February 19th 09 08:57 PM
Countif expanded brownti via OfficeKB.com Excel Discussion (Misc queries) 3 December 10th 08 06:35 PM
Expanded sumproduct Steven Excel Worksheet Functions 12 October 30th 08 10:56 PM
Expanded Vlookup Brad Excel Discussion (Misc queries) 2 March 28th 08 06:53 PM
Can the function window be expanded? Bill Excel Worksheet Functions 2 July 30th 07 09:21 PM


All times are GMT +1. The time now is 02:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"