Formula instead of Using Pivot table

[webmax]

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

 

[Somendra Misra]

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,

 

[webmax]

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

 

[Somendra Misra]

Hi,

Can you explain the logic here.

Regards,

 

[webmax]

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/-