Here's a VBA method.. this goes to the sheet module of course...
Regards,
Sam Mathai Chacko
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, ActiveSheet.Cells.Find("Top
Poster").Resize(11, 2)) Is Nothing Then
Application.EnableEvents = 0
Application.ScreenUpdating = 0
SortChart
Application.EnableEvents = 1
Application.ScreenUpdating = 1
End If
End Sub
Sub SortChart()
Dim varArray As Variant
Dim varStore As Variant
Dim rngCells As Range
Dim strSource As String
Dim strSeries As String
Dim strAxisValues As String
Dim strValues As String
Dim lngLoop As Long
varArray = ActiveSheet.Cells.Find("Top Poster").Resize(11,
3).Formula
Set rngCells = ActiveSheet.Cells.Find("Top Poster").Resize(11, 3)
With ActiveWorkbook.Worksheets("discussexcel").Sort
.SortFields.Clear
.SortFields.Add Key:=rngCells.Cells(2,
2).Resize(rngCells.Rows.Count - 1), SortOn:=0, Order:=1, DataOption:=0
.SortFields.Add Key:=rngCells.Cells(2,
1).Resize(rngCells.Rows.Count - 1), SortOn:=0, Order:=2, DataOption:=0
.SetRange rngCells
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
varStore = rngCells.Value
rngCells.Formula = varArray
For lngLoop = 2 To UBound(varStore, 1)
strAxisValues = strAxisValues & """" & varStore(lngLoop, 1) &
""","
Next lngLoop
For lngLoop = 2 To UBound(varStore, 1)
strValues = strValues & varStore(lngLoop, 2) & ","
Next lngLoop
strAxisValues = "{" & Left(strAxisValues, Len(strAxisValues) - 1)
& "}"
strValues = "{" & Left(strValues, Len(strValues) - 1) & "}"
strSource = "=SERIES(""Top Poster""," & strAxisValues & "," &
strValues & ",1)"
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Formula =
strSource
Erase varArray
Erase varStore
Set rngCells = Nothing
strSource = vbNullString
strSeries = vbNullString
strAxisValues = vbNullString
strValues = vbNullString
lngLoop = Empty
End Sub
On Sep 17, 6:57 pm, Ayush Jain <jainayus...@gmail.com> wrote:
> Thanks Ashish , It does work well. However I used the rank function to sort
> the data in different range.
> Sam, All the solutions are most welcome.
>
> I am looking at the solutions where we can sort chart without using the
> additional range. Sometimes the complex sheets with large number of charts
> dont allow to add seperate range to sheets.
>
> All ideas matters :) :)
>
> Regards
> Ayush Jain
>
> On Sat, Sep 17, 2011 at 6:18 PM, Sam Mathai Chacko <samde...@gmail.com>wrote:
>
>
>
>
>
> > Ashish, nice improvisation.
>
> > Ayush, has that solution from solved your question? or are you looking for
> > any other solution
>
> > Sam
>
> > On Sat, Sep 17, 2011 at 5:47 PM, ashish koul <koul.ash...@gmail.com>wrote:
>
> >> check the attachement
>
> >> On Sat, Sep 17, 2011 at 5:33 PM, Ayush Jain <jainayus...@gmail.com>wrote:
>
> >>> Dear group,
>
> >>> Recently in one of the project, I encountered an issue where we need to
> >>> sort the 2-D Bar chart from high to low without sorting the data table
> >>> The data table could not be sorted because it is linked with some dynamic
> >>> formulas.
>
> >>> Enclosed is the sample data and chart for you, where I jumbled the
> >>> forum top posters data in data table.
> >>> So the CHALLENGE for you is that you need to sort bars in chart from high
> >>> to low without sorting data.
>
> >>> Let's see who has the solution.
> >>> Keep posting !
>
> >>> Best regards,
> >>> Ayush Jain
> >>> Group Manager
>
> >>> P.S. Can we use some dynamic formulas in chart series range, I dont know,
> >>> I am just thinking. :)
>
> >>> --
>
> >>> ----------------------------------------------------------------------------------
> >>> Some important links for excel users:
> >>> 1. Follow us on TWITTER for tips tricks and links :
> >>>http://twitter.com/exceldailytip
> >>> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> >>> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> >>> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> >>> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> >>> To post to this group, send email to excel-macros@googlegroups.com
>
> >>> <><><><><><><><><><><><><><><><><><><><><><>
> >>> Like our page on facebook , Just follow below link
> >>>http://www.facebook.com/discussexcel
>
> >> --
> >> *Regards*
> >> * *
> >> *Ashish Koul*
> >> *http://www.excelvbamacros.com/*
>
> >> P Before printing, think about the environment.
>
> >> --
>
> >> ----------------------------------------------------------------------------------
> >> Some important links for excel users:
> >> 1. Follow us on TWITTER for tips tricks and links :
> >>http://twitter.com/exceldailytip
> >> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> >> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> >> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> >> To post to this group, send email to excel-macros@googlegroups.com
>
> >> <><><><><><><><><><><><><><><><><><><><><><>
> >> Like our page on facebook , Just follow below link
> >>http://www.facebook.com/discussexcel
>
> > --
> > Sam Mathai Chacko
>
> > --
>
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/discussexcel
>
> --
> Best regards,
> Ayush Jain- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
