Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I make a pivot table, through which I show a Date & SaleAmount. I want to creat a button, when I click on this button the table show me the data as an ascending order or vice versa. Please help me...
Regards,
Anup
HI,
What dimension you want to sort and by what order. Specify the order of the dimension.
Regards,
Kaushik Solanki
Hi,
Koushik as a dimenssion I use Date & I sort it as a decending order. But I want make a button through which I want to change the order of my date as an ascending order. That means I want to give the two option to the user who want to view this table!!!!!! Thank You for your responce.
Regards,
Anup
Hi,
I assume that the first dimension is Date.
Here is the macro
Sub SortAsc
chart=ActiveDocument.GetSheetObject("CH01")
set Prop = chart.GetProperties
set vars = Prop.Dimensions
vars.Item(0).SortCriteria.SortByNumeric = 1 'For Asc.
chart.SetProperties Prop
End sub
Sub SortDesc
chart=ActiveDocument.GetSheetObject("CH01")
set Prop = chart.GetProperties
set vars = Prop.Dimensions
vars.Item(0).SortCriteria.SortByNumeric = -1 'For Desc.
chart.SetProperties Prop
End sub
Now create two button one for asc and second for desc.
In first button give action as external->macro and give name as SortAsc.
Same way for Desc.
Regards,
Kaushik Solanki
Hi Anup,
It's easier to change the field to sort by than to flip the sort order but try creating 2 variables:
VSortOrder with a value of 1
vSort with a value of =DateField
Then, create a button with 2 sequential actions, both External > Set Variable:
1. vSort - IF(vSortOrder=1,'=DateField*-1','=DateField')
2. vSortOrder - IF(vSortOrder=1,-1,1)
In the sort tab of your pivot table, check the option to use an expression and put vSort in there (might need an = sign)
I've not been able to test this so hope it works for you!
Jason
Thank You Koushik. Is it possible with out macro???
Hi,
Below attached a working example.
HTH
Reg,
Shubhu
Thank You to all of you..........