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'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.

Sorry my english is not very good. Could you explain it simply please

 

[awoohaw]

Sorry my english is not very good. Could you explain it simply please

Keep your raw data plain. use other worksheets to report and manipulate.

 

[Papasmurfi]

Keep your raw data plain. use other worksheets to report and manipulate.

Ah ok, got it. Can you fix these things for me? Then I can continue working that way. I still don't know much about Excel. Started going to a class. I will be able to understand after some time.

 

[awoohaw]

can you get the xl2bb add in (link below) so you can post some sample worksheets?
Create fake names to protect confidentiality.

never mind, you've shared the workbook in other posts, my bad!

 

[awoohaw]

okay, here is an updated file. I used the same name, i thought it would let me change it. You'll see two new worksheets.
I hope you can figure it out.

553 KB file on MEGA

mega.nz

 

[Papasmurfi]

okay, here is an updated file. I used the same name, i thought it would let me change it. You'll see two new worksheets.
I hope you can figure it out.

553 KB file on MEGA

mega.nz

When i open this with office 365 on phone it works. But when i try to use this on pc it doesn’t work. I get the NAME error other than it works fine.

 

[awoohaw]

It uses Office 365 functions, which I thought worked in 2021 (as what your button says). What version is on you PC?

 

[awoohaw]

ah. i see a post that says you have 2019. Ugh. You need to change that!

 

[Papasmurfi]

ah. i see a post that says you have 2019. Ugh. You need to change that!

Yes will change that now sorry. I wanted to asked you, is this formula works when i add new loans to the “PORTFOLIO” and to “DAILY COLLECTION” or do i need to type the formula to one by one??

 

[awoohaw]

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.