Creating Pivot Table using Database

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

chandrashekar

Active Member
Joined
Jul 15, 2005
Messages
458

ADVERTISEMENT

Hi,

In tried below code but here I am getting a problem i.e I am trying to add all subject total and getting percentage out of it.

1) pvt_tbl.PivotFields("Marks Total") and
2) pvt_tbl.PivotFields("Percentages Total")

In report I am getting in the same row that is H2 and H3
H2= "Sum of Marks Total" and
H3 = "Sum of Percentages Total"

But I need this field in the column wise i.e.
"Sum of Marks Total" in H Column
"Sum of Percentages Total" in I Column

*If u need I will attach excel file.

Please help me out

Sub pivot_Acc2Xl()
Dim dbs_nm, ssql As String
Dim dbs_conn As ADODB.Connection
Dim dbs_rs As ADODB.Recordset
Dim pvt_tbl As PivotTable
Dim pvt_che As PivotCache

dbs_nm = "C:\Documents and Settings\Chandru\Desktop\test.mdb"
Set dbs_conn = New ADODB.Connection
Set dbs_rs = New ADODB.Recordset
With dbs_conn
.Provider = "Microsoft.jet.oledb.4.0"
.Open dbs_nm
End With
Set dbs_rs.ActiveConnection = dbs_conn
ssql = "Select * from table1"
dbs_rs.Open ssql

Set pvt_che = ThisWorkbook.PivotCaches.Add(xlExternal)
Set pvt_che.Recordset = dbs_rs
Set pvt_tbl = pvt_che.CreatePivotTable(Worksheets(3).Range("A1"))

With pvt_tbl
.SmallGrid = False
.AddFields Array("Stud Nm", "Class", "Kannada", "English", "Maths", "Science", "Social")
End With

With pvt_tbl
.CalculatedFields.Add "Marks Total", "=sum(Kannada+English+Maths+Science+Social)"
.CalculatedFields.Add "Percentages Total", "=Marks Total /600*100"
End With

With pvt_tbl
.ColumnGrand = False
.RowGrand = False
.InGridDropZones = False
.TableStyle2 = "PivotStyleLight17"
.DisplayFieldCaptions = True
End With

With pvt_tbl.PivotFields("Marks Total")
.Orientation = xlDataField
.NumberFormat = "#,##0.00"
.LayoutForm = xlTabular
End With


With pvt_tbl.PivotFields("Percentages Total")
.Orientation = xlDataField
.NumberFormat = "#,##0.00"
.LayoutForm = xlTabular
End With


On Error Resume Next
For i = 2 To pvt_tbl.PivotFields.Count
a = pvt_tbl.PivotFields(i).Name
With pvt_tbl.PivotFields(a)
.LayoutForm = xlTabular
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With
Next
On Error GoTo 0

With pvt_tbl
.PivotFields("Stud Nm").LayoutBlankLine = True
.NullString = "0"
.ShowDrillIndicators = False
.ColumnGrand = False
.RowGrand = False
.InGridDropZones = False
.PivotFields("Sum of Marks Total").Caption = " Marks Total"
.PivotFields("Sum of Percentages").Caption = " Percentage"
.TableStyle2 = "PivotStyleLight17"
.DisplayFieldCaptions = True
End With


With pvt_tbl
pitm_cnt = pvt_tbl.PivotFields("Stud Nm").PivotItems.Count
For j = 1 To pitm_cnt
pitem = pvt_tbl.PivotFields("Stud Nm").PivotItems(j)
For i = 1 To pitm_cnt
pvtitem = pvt_tbl.PivotFields("Stud Nm").PivotItems(i)
If pvtitem = pitem Then
pvt_tbl.PivotFields("Stud Nm").PivotItems(pvtitem).Visible = True
Else
pvt_tbl.PivotFields("Stud Nm").PivotItems(pvtitem).Visible = False
End If
Next
.TableRange1.Offset(1, 0).Copy
add_sht (pitem)
For Each pvtfld In pvt_tbl.PivotFields("Stud Nm").PivotItems
pvtfld.Visible = True
Next
i = i + 1

Next
End With


Set pvt_che = Nothing
Set pvt_tbl = Nothing
Set dbs_conn = Nothing
Set dbs_rs = Nothing
End Sub
Sub add_sht(ByRef pitem As String)
ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
ThisWorkbook.Worksheets(Worksheets.Count).Name = pitem
With ThisWorkbook.Worksheets(pitem)

.Range("A3").PasteSpecial xlPasteValuesAndNumberFormats
.Range("A2:I2") = Array("Stud Nm", "Class", "Kannada", "English", "Maths", "Science", "Social", "Marks", "Percentange")
.Range("A1") = "Progess Report of " & pitem
.Range("A1:I1").Merge
.Range("A1:I1").HorizontalAlignment = xlCenter
.Range("A1:I1").Font.ColorIndex = 45
.Range("A1").Font.Size = 15
.Range("A1").Font.Bold = True
.Range("A2:I2").Font.Bold = True
.Range("A2:I2").Font.ColorIndex = 55
.Range("A1").Select
.Columns.AutoFit
End With

With Worksheets(3)
Application.CutCopyMode = False
End With
End Sub


Regards,

Chandra Shekar B
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is this what you want?

<TABLE style="WIDTH: 380pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=505 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 width=64> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=56> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 width=51>Data</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=49> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=58> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=54> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=50> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl23 width=44> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl26 width=79> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 25.5pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=34>Stud Nm</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>Class</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl31 width=51>Sum of English</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl32 width=49>Sum of Maths</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl32 width=58>Sum of Kannada</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl32 width=54>Sum of Science</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl32 width=50>Sum of Social</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl32 width=44>Marks Total</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl33 width=79>Percentages Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>a</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>23</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>60</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>89</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>46</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>230</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.15323117921385743">15.32%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>b</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>53</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>25</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>61</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>37</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>33</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>209</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.13924050632911392">13.92%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>c</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>51</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>75</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>91</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>83</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>305</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.20319786808794138">20.32%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>d</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>7</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>58</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>94</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>85</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>245</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.16322451698867421">16.32%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>e</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>35</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>90</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>89</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>24</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>250</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.16655562958027981">16.66%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 align=right x:num>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22>f</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl22 align=right x:num>94</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>37</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>14</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>38</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>79</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl27 align=right x:num>262</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl29 align=right x:num="0.17455029980013326">17.46%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" class=xl24 height=17 colSpan=2>Grand Total</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl24 align=right x:num>177</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>178</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>358</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>438</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>350</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl28 align=right x:num>1501</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl30 align=right x:num="1">100.00%</TD></TR></TBODY></TABLE>

The Percentages Total field has the same formula as the Marks Total field, but the data is shown as % of column.
 

chandrashekar

Active Member
Joined
Jul 15, 2005
Messages
458

ADVERTISEMENT

Hi,

Thanks for your reply.

Exactly here I don't need Sum of but I need only subject name for ex. english etc.

Regards,

Chandra Shekar
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I don't think it's possible to name a data field the same as the name in the source data.
 

chandrashekar

Active Member
Joined
Jul 15, 2005
Messages
458
Hi,

Thank u for your suggestion I also tried but I could not able to do it.

1) How can I simplify this code?
2) I need to insert chart for each worksheet, how to do it?

Please help me out.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I'm not sure about simplifying the code, but did you try recording a macro while adding a pivot chart?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,494
Messages
5,511,658
Members
408,859
Latest member
willm57

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top