• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula instead of Using Pivot table

webmax

Member
Hi

Attaching the sample file with Four sheets

1) Dashboard
2) Data
3) Pivot 1
4) Pivot 4

My requirement is to pull the raw data and fill in to the dashboard sheet.

It is an accounting double entry system ie debit and Credit

Now for pulling the data i have to put the pivot table two times and using vlookup. I want using any formula without pivot table i need to fill the data in the dashboard.

In the Datasheet the two column from user and To User

In the First record Mr. John is providing Rs.2000/- to Mr. Peter in the Dashboard sheet under Username John Rs. 2000 to be shown in the Debited column and Rs.2000/- to be shown under username Peter in the Credited column.

And also in the Data sheet fourth column Transaction is credit means this scenario and if it is debit means then it is viceversa.

Example Mr. John is Debited Rs.2000/- to Mr. Peter in the Dashboard sheet under Username John Rs. 2000 to be shown in the Credited column and Rs.2000/- to be shown under username Peter in the Debited column.

Regards
Shahul
 

Attachments

  • FUNDTRANSFERDATA.xlsx
    14.9 KB · Views: 2
Last edited:
Hi,

See this file. Blue cells on Dashboard sheet has formula. I had simply use a SUMIF function, because logic seems to me simple. If that is not the case and there is more complexity in it, please write back.

Regards,
 

Attachments

  • FUNDTRANSFERDATA.xlsx
    16.5 KB · Views: 3
Hi

Now i add one scenario as Debit in the Transaction column.

And also i have applied one more vlookup and adding the value.

Kindly go to the revised attached file and provide the formula

Regards
Shahul
 

Attachments

  • FUNDTRANSFERDATA.xlsx
    17.9 KB · Views: 8
Hi
The logic is below

In the second entry Mr. Peter is Crediting Rs. 3000/- to Mr. RAM.
So credit to Mr. Ram and Debit to Mr. Peter for Rs.3000/-

In the third entry Mr. Peter is reverting the amount of Rs. 1000/- from Mr. Ram. So Credit to Mr. Peter and Debit t Mr. Ram for Rs.1000/-
 
Back
Top