Pivot Table

J

john5599

Board Regular
Joined
Mar 11, 2010
Messages
222
I have an excel sheet as below

Excel 2007
ABCDEFGHIJK
2DateCustomer NameNarrationAmountCash RecivedCheque RecivedType
305/12/2012Madan PuraBill No.156515000InvoiceBill NoBill NoInvoice
405/12/2012Jerry Pvt. LtdBill No.145610000InvoiceSales rSales returnSales Return
505/12/2012Jackson Pvt. LtdBill No.145825000InvoiceCash TrCash Transfer to Bank A/CCash Transfer
605/12/2012Jackson Pvt. LtdCash Received25000Cash ReceivedDiscounDiscount GivenDiscount
718/12/2012Jackson Pvt. LtdCheque Received1800Cheque ReceivedChequeCheque ReceivedCheque Received
805/12/2012Rupa & CompanyBill No.15607500InvoiceCash ReCash ReceivedCash Received
905/12/2012Vidhya Sagar Pvt. Ltd.Bill No.162015200InvoiceGoods RGoods ReturnSales Return
1005/12/2012Mahesh & SonsBill No.16223500InvoiceCash T/Cash T/F to BankCash Transfer
1105/12/2012Mruthula & Co.Bill No.17616500Invoice
1205/12/2012Markos Bros.Bill No.176210200Invoice
1305/12/2012Madan PuraBill No.176415300Invoice
1405/12/2012Jimmy TradersBill No.163010700Invoice
1505/12/2012Varun AssociatesBill No.17634500Invoice
1605/12/2013Varun AssociatesCash Received By5000Cash Received
1705/12/2012George Peter GroupsBill No.178010300Invoice
1805/12/2012Vikram AssociatesBill No.18631050Invoice
1915/05/2012Jerry Pvt. LtdBill No.18341000Invoice
2015/05/2012Rupa & CompanyBill No.1865750Invoice
2115/05/2012Mruthula & Co.Bill No.1890400Invoice
2216/05/2012Rupa & CompanyCash Transfer to Bank A/C7000Cash Transfer
2315/05/2012Varun AssociatesBill No.1925700Invoice
2415/05/2012Rupa & CompanyBill No.18968000Invoice
2518/05/2013Jackson Pvt. LtdBill No.97530000Invoice
2615/05/2012Mahesh & SonsBill No.19251400Invoice
2715/05/2012Jimmy TradersBill No.184712000Invoice
2815/05/2012Vidhya Sagar Pvt. Ltd.Bill No.19481020Invoice
2915/05/2012Vikram AssociatesBill No.1884500Invoice
3018/05/2012Markos Bros.Bill No.19271020Invoice
3118/05/2012Jackson Pvt. LtdBill No.20605220Invoice
3218/05/2012George Peter GroupsBill No.24354500Invoice
3318/05/2012Jimmy TradersBill No.19451800Invoice
3418/05/2012Madan PuraBill No.17908000Invoice
3520/05/2012Rupa & CompanyBill No.15361110Invoice
3620/05/2012Vikram AssociatesBill No.15251600Invoice
3720/05/2012Jerry Pvt. LtdBill No.15856660Invoice
3820/05/2012Varun AssociatesBill No.18903321Invoice
3920/05/2012Mahesh & SonsBill No.18643380Invoice
4025/05/2012George Peter GroupsBill No.1924839Invoice
4125/05/2012Vidhya Sagar Pvt. Ltd.Bill No.1938332Invoice
4212/06/2012Jackson Pvt. LtdSales return1800Sales Return
4325/07/2012Jackson Pvt. LtdCash Transfer3600Cash Transfer
4418/08/2012Jackson Pvt. LtdGoods Return360Sales Return
4518/05/2013Jackson Pvt. LtdDiscount Given350Discount
4620/05/2013Mahesh & SonsCash T/F to Bank1200Cash Transfer
4720/05/2013George Peter GroupsCheque Received12000Cheque Received
4810/06/2013George Peter GroupsGoods Return985Sales Return
Invoice-Cash-Cheque
Cell Formulas
RangeFormula
I3=LEFT(J3,7)
I4=LEFT(J4,7)
I5=LEFT(J5,7)
I6=LEFT(J6,7)
I7=LEFT(J7,7)
I8=LEFT(J8,7)
I9=LEFT(J9,7)
I10=LEFT(J10,7)
G3=VLOOKUP(LEFT(C3,7),$I$3:$K$10,3,FALSE)
G4=VLOOKUP(LEFT(C4,7),$I$3:$K$10,3,FALSE)
G5=VLOOKUP(LEFT(C5,7),$I$3:$K$10,3,FALSE)
G6=VLOOKUP(LEFT(C6,7),$I$3:$K$10,3,FALSE)
G7=VLOOKUP(LEFT(C7,7),$I$3:$K$10,3,FALSE)
G8=VLOOKUP(LEFT(C8,7),$I$3:$K$10,3,FALSE)
G9=VLOOKUP(LEFT(C9,7),$I$3:$K$10,3,FALSE)
G10=VLOOKUP(LEFT(C10,7),$I$3:$K$10,3,FALSE)
G11=VLOOKUP(LEFT(C11,7),$I$3:$K$10,3,FALSE)
G12=VLOOKUP(LEFT(C12,7),$I$3:$K$10,3,FALSE)
G13=VLOOKUP(LEFT(C13,7),$I$3:$K$10,3,FALSE)
G14=VLOOKUP(LEFT(C14,7),$I$3:$K$10,3,FALSE)
G15=VLOOKUP(LEFT(C15,7),$I$3:$K$10,3,FALSE)
G16=VLOOKUP(LEFT(C16,7),$I$3:$K$10,3,FALSE)
G17=VLOOKUP(LEFT(C17,7),$I$3:$K$10,3,FALSE)
G18=VLOOKUP(LEFT(C18,7),$I$3:$K$10,3,FALSE)
G19=VLOOKUP(LEFT(C19,7),$I$3:$K$10,3,FALSE)
G20=VLOOKUP(LEFT(C20,7),$I$3:$K$10,3,FALSE)
G21=VLOOKUP(LEFT(C21,7),$I$3:$K$10,3,FALSE)
G22=VLOOKUP(LEFT(C22,7),$I$3:$K$10,3,FALSE)
G23=VLOOKUP(LEFT(C23,7),$I$3:$K$10,3,FALSE)
G24=VLOOKUP(LEFT(C24,7),$I$3:$K$10,3,FALSE)
G25=VLOOKUP(LEFT(C25,7),$I$3:$K$10,3,FALSE)
G26=VLOOKUP(LEFT(C26,7),$I$3:$K$10,3,FALSE)
G27=VLOOKUP(LEFT(C27,7),$I$3:$K$10,3,FALSE)
G28=VLOOKUP(LEFT(C28,7),$I$3:$K$10,3,FALSE)
G29=VLOOKUP(LEFT(C29,7),$I$3:$K$10,3,FALSE)
G30=VLOOKUP(LEFT(C30,7),$I$3:$K$10,3,FALSE)
G31=VLOOKUP(LEFT(C31,7),$I$3:$K$10,3,FALSE)
G32=VLOOKUP(LEFT(C32,7),$I$3:$K$10,3,FALSE)
G33=VLOOKUP(LEFT(C33,7),$I$3:$K$10,3,FALSE)
G34=VLOOKUP(LEFT(C34,7),$I$3:$K$10,3,FALSE)
G35=VLOOKUP(LEFT(C35,7),$I$3:$K$10,3,FALSE)
G36=VLOOKUP(LEFT(C36,7),$I$3:$K$10,3,FALSE)
G37=VLOOKUP(LEFT(C37,7),$I$3:$K$10,3,FALSE)
G38=VLOOKUP(LEFT(C38,7),$I$3:$K$10,3,FALSE)
G39=VLOOKUP(LEFT(C39,7),$I$3:$K$10,3,FALSE)
G40=VLOOKUP(LEFT(C40,7),$I$3:$K$10,3,FALSE)
G41=VLOOKUP(LEFT(C41,7),$I$3:$K$10,3,FALSE)
G42=VLOOKUP(LEFT(C42,7),$I$3:$K$10,3,FALSE)
G43=VLOOKUP(LEFT(C43,7),$I$3:$K$10,3,FALSE)
G44=VLOOKUP(LEFT(C44,7),$I$3:$K$10,3,FALSE)
G45=VLOOKUP(LEFT(C45,7),$I$3:$K$10,3,FALSE)
G46=VLOOKUP(LEFT(C46,7),$I$3:$K$10,3,FALSE)
G47=VLOOKUP(LEFT(C47,7),$I$3:$K$10,3,FALSE)
G48=VLOOKUP(LEFT(C48,7),$I$3:$K$10,3,FALSE)

I have a Pivot Table like below

Excel 2007
ABCDEFGH
2
3Sum of Field1Column Labels
4Row LabelsCash ReceivedCash TransferCheque ReceivedDiscountInvoiceSales ReturnGrand Total
5George Peter Groups001200001563998528624
6Jackson Pvt. Ltd250003600180035060220216093130
7Jerry Pvt. Ltd000017660017660
8Jimmy Traders000024500024500
9Madan Pura000038300038300
10Mahesh & Sons0120000828009480
11Markos Bros.000011220011220
12Mruthula & Co.0000690006900
13Rupa & Company070000017360024360
14Varun Associates50000008521013521
15Vidhya Sagar Pvt. Ltd.000016552016552
16Vikram Associates0000315003150
17Grand Total3000011800138003502283023145287397
18
19
PivotTable



<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
I need to add one Balance column in pivot table

Balance should come
= Invoice(-)Cash Received,Cheque Received,Cash Transfer,Sales Return,Discount

How? Is it possible
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Are you saying that you weren't able to get it to work following the steps in the link?

If not, what happened when you tried?
 
Upvote 0
Try getting an easier formula to work first. Once that works and you know how to make a calculated field, we can fix the formula.

Try making a field called "Test" with just this formula:
=Invoice- 'Cash Received'
 
Upvote 0
Sir first Inserted Calculated then named it test then in the formula I paste =Invoice- 'Cash Received' then also it "Formula you typed contains error.

Sir can you just try?
 
Upvote 0
I don't think that I can do a better job explaining the steps than the illustrated article linked in Post #2.

If you'll upload an example file to a hosting site like Box.com and post a link, I'll add the calculated field so you can see what that should be.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
310
awoohaw
awoohaw
M
  • Question
Bifurcate total expenses on the basis of calendar months other than AD
Replies
2
Views
384
MandeepBajimaya
M
G
  • Question
Find Values that Match Criteria from Different Sheet and Copy over to Another
Replies
0
Views
136
glaccounting
G
94mustang
  • Solved
Conditional Formatting on Quarterly Dates
Replies
7
Views
2K
Micron
M
N
  • Question
Managing Payment table separate from billing, calculating lps on every received payment based on criteria.
Replies
1
Views
415
NaushadAnwar
N

Forum statistics

Threads
1,214,912
Messages
6,122,200
Members
449,072
Latest member
DW Draft

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top