Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
toolbar not expanded | Excel Discussion (Misc queries) | |||
Countif expanded | Excel Discussion (Misc queries) | |||
Expanded sumproduct | Excel Worksheet Functions | |||
Expanded Vlookup | Excel Discussion (Misc queries) | |||
Can the function window be expanded? | Excel Worksheet Functions |