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

[Papasmurfi]

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

 

[Papasmurfi]

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

 

[awoohaw]

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.

 

[Papasmurfi]

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.

 

[awoohaw]

ok. Here is the v2019 version of the earlier file:

1.15 MB folder on MEGA

2 files

mega.nz

Mr. Excel has a paid services / references link.

 

[awoohaw]

here is the consulting services link:

Consulting Services

Are you stuck doing a tedious manual process day after day? This can be automated using VBA macros behind your Excel worksheets. For over twenty years, these Excel consultants work with customers via e-mail or telephone to build a solution.

www.mrexcel.com

 

[Papasmurfi]

here is the consulting services link:

Consulting Services

Are you stuck doing a tedious manual process day after day? This can be automated using VBA macros behind your Excel worksheets. For over twenty years, these Excel consultants work with customers via e-mail or telephone to build a solution.

www.mrexcel.com

Thank you so much. Appreciate it.

 

[awoohaw]

you're welcome. Please see the v2019 file for a version that should work on your desktop.