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

 

[Peter_SSs]

Welcome to the MrExcel board!

Firstly, these two things do not agree with each other. perhaps you could clarify that?

Secondly very hard for anybody to suggest anything with seeing the layout of the sheet that the data has to be pulled from (DAILY COLLECTIONS). I suggest that you give us some small sample dummy data for that sheet with XL2BB so that helpers can copy it for testing purposes.

 

[Papasmurfi]

Welcome to the MrExcel board!

Firstly, these two things do not agree with each other. perhaps you could clarify that?
View attachment 91577

View attachment 91578

Secondly very hard for anybody to suggest anything with seeing the layout of the sheet that the data has to be pulled from (DAILY COLLECTIONS). I suggest that you give us some small sample dummy data for that sheet with XL2BB so that helpers can copy it for testing purposes.

Daily Collection New PortfolioSorry, When i register to this site i been trying to update the office 2021. thats why i add my version as 2021. i couldn't update the office. so still i'm using 2019 version of office. i have attached the excel file here. Hope it would be easier to find a solution.

 

[Papasmurfi]

Daily Collection New PortfolioSorry, When i register to this site i been trying to update the office 2021. thats why i add my version as 2021. i couldn't update the office. so still i'm using 2019 version of office. i have attached the excel file here. Hope it would be easier to find a solution.

here is a another link in case google ask for permission. Daily Collection New Portfolio

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to create a payment record to be appear upon entering of the loan number
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Papasmurfi]

I’m am sorry I didn’t know about it. So sorry if make people upset. I was unaware of it. I can assure you i won’t happend again.

 

[awoohaw]

I'm confused with your payment history data? Are you getting payments daily from the debtors and not monthly?
Can you manually give some examples of what a payment history (with dates and amounts) would look like?

 

[Papasmurfi]

I'm confused with your payment history data? Are you getting payments daily from the debtors and not monthly?
Can you manually give some examples of what a payment history (with dates and amounts) would look like?

These are microloans where debtors make daily payments to me. For instance, if I lend someone 10,000 for a one-month period consisting of 25 working days, with an interest rate of 10%, the total amount owed would be 11,000. Dividing 11,000 by 25, I would collect 440 from the debtor on a daily basis.

I want to pull the paid dates and amounts from the 'DAILY COLLECTION' to the 'STAT FORM' upon entering the loan number.

Let's say, for example, when I enter the loan number 'DLY01,' all the corresponding paid dates and amounts for that loan number need to appear in the order of paid dates."

 

[awoohaw]

ok. thanks. i'll look at it.

 

[awoohaw]

These are microloans where debtors make daily payments to me. For instance, if I lend someone 10,000 for a one-month period consisting of 25 working days, with an interest rate of 10%, the total amount owed would be 11,000. Dividing 11,000 by 25, I would collect 440 from the debtor on a daily basis.

I want to pull the paid dates and amounts from the 'DAILY COLLECTION' to the 'STAT FORM' upon entering the loan number.

Let's say, for example, when I enter the loan number 'DLY01,' all the corresponding paid dates and amounts for that loan number need to appear in the order of paid dates."

i'm looking at the daily collections tab... I'm not sure how easy this will be since you have text in the columns ("ARREARS").
And what is the significance of the different formatting? Should any of that make a difference.

Just from a data perspective, and a suggestion, your payments should be a data source to itself and feed all of your reporting. Which is sort of what you are doing with your question, but if the formatting and handling of the "DAILY COLLECTIONS" worksheet is more than just recording the payments you are compounding problems.