How to create a payment record to be appear upon entering of the loan number.

Papasmurfi

New Member
Joined
May 12, 2023
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
Hello everyone 🤚

I need a huge favor. For the past five days, I have been trying to find a solution to an issue I am having.

As shown in the first picture "Shown in the pic as number 1", when I enter a loan number, I need to get the paid record in these columns "shown in the pic as number 2".

The paid details are on another sheet "as DAILY COLLECTIONS".

3rd Raw Highlighted in “Purple color” shows the dates and vertically shows the paid amounts corresponding to the loan number.

So, the issue I am having is how to get the corresponding details to the "STAT FORM" sheet upon entering the loan number.

I need to get the paid details as to the stat form “Upon entering loan number”, like a system when you enter loan number and paid details appear as a payment record in STAT Form sheet.

If anyone know a way to make this work. Any formulas or any tricks tips

I’m new to excel and don’t know much about formulas. But i like to learn

FYI - I’m using office 2019
 

Attachments

  • 91e73221-d7f1-4c4a-9f74-edb3c70deaf2.jpeg
    91e73221-d7f1-4c4a-9f74-edb3c70deaf2.jpeg
    231.8 KB · Views: 24
I only did this:
1. Put payment history on a new worksheet
2. Did a formula to get the history for a specific number.

I did not do anything that moves data from the original worksheet to the new one.

Are you going to use this only in 365/phone or on your PC? If not you still need a solution.
I mainly use pc. I cannot purchase 365 or 2021 any time soon. Yes i still need a solution 😕
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
It seems to me you are trying to create an application. Which EXCEL can certainly do, but other applications like MS-Access are more suitable.
From looking at your workbook. You need to separate transactional data from static data.
I'd suggest a few different sheets:
1. Customer Sheet, has customer information, including some kind of identifier (a number or alphanumeric code) as the customer.
2. Loan Sheet, details on the loan, but not payment history. It can have summaries of payments and current balance. Should be tied back to the customer with the alphanumeric customer code.
3. Transactions sheet. Transactions (payments, loan advances, interest earned) as they apply to the loan and data about the transactions. Would be linked to the loan by the loan number/ID.
4. If you take collateral there should be a separate collateral sheet and ID as well. Linked to the owner and also linkted to the loans.

I have no idea what the expenses sheet is for. If that is expenses per loan then you can put that in the transactions as well... but then you would need to have codes to separate them from payments/loan advances.

There are tools in Excel called Power Pivot and Power Query that can really help you with this. I am not an expert, and there is a forum here at Mr. Excel called "Power Tools" that you can get help from. These are in 2019.
 
Upvote 0
It seems to me you are trying to create an application. Which EXCEL can certainly do, but other applications like MS-Access are more suitable.
From looking at your workbook. You need to separate transactional data from static data.
I'd suggest a few different sheets:
1. Customer Sheet, has customer information, including some kind of identifier (a number or alphanumeric code) as the customer.
2. Loan Sheet, details on the loan, but not payment history. It can have summaries of payments and current balance. Should be tied back to the customer with the alphanumeric customer code.
3. Transactions sheet. Transactions (payments, loan advances, interest earned) as they apply to the loan and data about the transactions. Would be linked to the loan by the loan number/ID.
4. If you take collateral there should be a separate collateral sheet and ID as well. Linked to the owner and also linkted to the loans.

I have no idea what the expenses sheet is for. If that is expenses per loan then you can put that in the transactions as well... but then you would need to have codes to separate them from payments/loan advances.

There are tools in Excel called Power Pivot and Power Query that can really help you with this. I am not an expert, and there is a forum here at Mr. Excel called "Power Tools" that you can get help from. These are in 2019.
Do you have any reference to someone who can create the application like you said?? Expenses for my references, to check how much earned in a month.
 
Upvote 0
here is the consulting services link:
 
Upvote 0
here is the consulting services link:
Thank you so much. Appreciate it.
 
Upvote 0
you're welcome. Please see the v2019 file for a version that should work on your desktop.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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
Back
Top