Loose formula/value (when building graphs)
in [Functions]
|
Prev: Pre-configuring Pivot table "views"
Next: Remove Spaces
From: PvZ on 2 Jun 2010 03:48 Hi, (looks like my previous posting was lost . . . . ?) thanks to the help from this forum (Roger G. !), ==for using the same graph, for various worksheets==, I am now using a named range called: GraphRange, with a value of: =INDIRECT(INDEX(Graphs!$A$1:$A$20,Graphs!$B$1)&"!$F$6:$F$10") where the worksheet names are listed in A1:A20; the worksheet I want to use is listed in B1; the relevant data is in e.g. F6:F10. This works however only partially: when I use "GraphRange" in the field [Chart data range], when selecting data for the graph, the formula/value -as described above- is replaced by the outcome of the formula. So when I change the content of B1, the graph does NOT change (but when entering "GraphRange" again in [Chart data range] the graph does change . . . . Q: How can I keep the formula/value (GraphChange) in the [Chart data range] field (so that the graph changes with the change of content in B1 ??? (Remark: I have B1 changing through selection via 'Combo Box' from A1:A20) Thanks in advance, Paul
From: Roger Govier on 2 Jun 2010 05:18 Hi Paul Sorry, I didn't test it before posting. You're correct, it translates to the fixed range. Can you work with a VBA solution? If so then the following piece of event code might help. Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet, graphrange As Range If Target.Address <> "$B$1" Then Exit Sub Set ws = ThisWorkbook.Sheets(Range("B1").Text) Set graphrange = ws.Range("F6:F10") ActiveSheet.ChartObjects(1).Activate ActiveChart.SetSourceData Source:=graphrange, PlotBy _ :=xlColumns End Sub This assumes that you are entering the (selecting) the full Sheet name in cell B1, as opposed to the row number as i was advocating in my previous formula. To Install Copy the code as above Right click on sheet tab>View Code Paste code into white pane that appears Alt+F11 to return to Excel As it is Event code, whenever there is a change in cell B1, it will get triggered. -- ------- Regards Roger Govier "PvZ" <PvZ(a)discussions.microsoft.com> wrote in message news:5FCB218F-EE74-4371-A051-5CC74C66915D(a)microsoft.com... > Hi, > (looks like my previous posting was lost . . . . ?) > > thanks to the help from this forum (Roger G. !), > ==for using the same graph, for various worksheets==, > I am now using a named range called: GraphRange, with a value of: > =INDIRECT(INDEX(Graphs!$A$1:$A$20,Graphs!$B$1)&"!$F$6:$F$10") > where the worksheet names are listed in A1:A20; the worksheet I want > to use is listed in B1; the relevant data is in e.g. F6:F10. > > This works however only partially: when I use "GraphRange" in the > field [Chart data range], when selecting data for the graph, the > formula/value -as described above- is replaced by the outcome of > the formula. So when I change the content of B1, the graph does NOT > change (but when entering "GraphRange" again in [Chart data range] the > graph does change . . . . > Q: How can I keep the formula/value (GraphChange) in the [Chart data > range] > field (so that the graph changes with the change of content in B1 ??? > (Remark: I have B1 changing through selection via 'Combo Box' from A1:A20) > > Thanks in advance, > > Paul > > > > __________ Information from ESET Smart Security, version of virus > signature database 5164 (20100601) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > __________ Information from ESET Smart Security, version of virus signature database 5164 (20100601) __________ The message was checked by ESET Smart Security. http://www.eset.com
From: PvZ on 2 Jun 2010 06:10 Hi Roger, YES, this works ! Thanks. Although now the Combo Box outcome doesn't work anymore. Will try to sort that out. Thanks Again !! Paul "Roger Govier" wrote: > Hi Paul > > Sorry, I didn't test it before posting. > You're correct, it translates to the fixed range. > > Can you work with a VBA solution? > If so then the following piece of event code might help. > > Private Sub Worksheet_Change(ByVal Target As Range) > Dim ws As Worksheet, graphrange As Range > If Target.Address <> "$B$1" Then Exit Sub > Set ws = ThisWorkbook.Sheets(Range("B1").Text) > Set graphrange = ws.Range("F6:F10") > ActiveSheet.ChartObjects(1).Activate > ActiveChart.SetSourceData Source:=graphrange, PlotBy _ > :=xlColumns > End Sub > > > This assumes that you are entering the (selecting) the full Sheet name in > cell B1, as opposed to the row number as i was advocating in my previous > formula. > > To Install > Copy the code as above > Right click on sheet tab>View Code > Paste code into white pane that appears > Alt+F11 to return to Excel > > As it is Event code, whenever there is a change in cell B1, it will get > triggered. > -- > ------- > Regards > Roger Govier > > "PvZ" <PvZ(a)discussions.microsoft.com> wrote in message > news:5FCB218F-EE74-4371-A051-5CC74C66915D(a)microsoft.com... > > Hi, > > (looks like my previous posting was lost . . . . ?) > > > > thanks to the help from this forum (Roger G. !), > > ==for using the same graph, for various worksheets==, > > I am now using a named range called: GraphRange, with a value of: > > =INDIRECT(INDEX(Graphs!$A$1:$A$20,Graphs!$B$1)&"!$F$6:$F$10") > > where the worksheet names are listed in A1:A20; the worksheet I want > > to use is listed in B1; the relevant data is in e.g. F6:F10. > > > > This works however only partially: when I use "GraphRange" in the > > field [Chart data range], when selecting data for the graph, the > > formula/value -as described above- is replaced by the outcome of > > the formula. So when I change the content of B1, the graph does NOT > > change (but when entering "GraphRange" again in [Chart data range] the > > graph does change . . . . > > Q: How can I keep the formula/value (GraphChange) in the [Chart data > > range] > > field (so that the graph changes with the change of content in B1 ??? > > (Remark: I have B1 changing through selection via 'Combo Box' from A1:A20) > > > > Thanks in advance, > > > > Paul > > > > > > > > __________ Information from ESET Smart Security, version of virus > > signature database 5164 (20100601) __________ > > > > The message was checked by ESET Smart Security. > > > > http://www.eset.com > > > > > > > > __________ Information from ESET Smart Security, version of virus signature database 5164 (20100601) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > >
From: Roger Govier on 2 Jun 2010 07:24 Hi Paul Just use Data Validation for your dropdown, rather than the Combo Box. The Combo Box returns an index number of the sheet selected within the list. DV will give just the Sheet name -- ------- Regards Roger Govier "PvZ" <PvZ(a)discussions.microsoft.com> wrote in message news:B832B82D-F7C4-47F7-9EA6-5A1181932836(a)microsoft.com... > Hi Roger, > > YES, this works ! Thanks. > Although now the Combo Box outcome doesn't work anymore. > Will try to sort that out. > Thanks Again !! > > Paul > > "Roger Govier" wrote: > >> Hi Paul >> >> Sorry, I didn't test it before posting. >> You're correct, it translates to the fixed range. >> >> Can you work with a VBA solution? >> If so then the following piece of event code might help. >> >> Private Sub Worksheet_Change(ByVal Target As Range) >> Dim ws As Worksheet, graphrange As Range >> If Target.Address <> "$B$1" Then Exit Sub >> Set ws = ThisWorkbook.Sheets(Range("B1").Text) >> Set graphrange = ws.Range("F6:F10") >> ActiveSheet.ChartObjects(1).Activate >> ActiveChart.SetSourceData Source:=graphrange, PlotBy _ >> :=xlColumns >> End Sub >> >> >> This assumes that you are entering the (selecting) the full Sheet name in >> cell B1, as opposed to the row number as i was advocating in my previous >> formula. >> >> To Install >> Copy the code as above >> Right click on sheet tab>View Code >> Paste code into white pane that appears >> Alt+F11 to return to Excel >> >> As it is Event code, whenever there is a change in cell B1, it will get >> triggered. >> -- >> ------- >> Regards >> Roger Govier >> >> "PvZ" <PvZ(a)discussions.microsoft.com> wrote in message >> news:5FCB218F-EE74-4371-A051-5CC74C66915D(a)microsoft.com... >> > Hi, >> > (looks like my previous posting was lost . . . . ?) >> > >> > thanks to the help from this forum (Roger G. !), >> > ==for using the same graph, for various worksheets==, >> > I am now using a named range called: GraphRange, with a value of: >> > =INDIRECT(INDEX(Graphs!$A$1:$A$20,Graphs!$B$1)&"!$F$6:$F$10") >> > where the worksheet names are listed in A1:A20; the worksheet I want >> > to use is listed in B1; the relevant data is in e.g. F6:F10. >> > >> > This works however only partially: when I use "GraphRange" in the >> > field [Chart data range], when selecting data for the graph, the >> > formula/value -as described above- is replaced by the outcome of >> > the formula. So when I change the content of B1, the graph does NOT >> > change (but when entering "GraphRange" again in [Chart data range] the >> > graph does change . . . . >> > Q: How can I keep the formula/value (GraphChange) in the [Chart data >> > range] >> > field (so that the graph changes with the change of content in B1 >> > ??? >> > (Remark: I have B1 changing through selection via 'Combo Box' from >> > A1:A20) >> > >> > Thanks in advance, >> > >> > Paul >> > >> > >> > >> > __________ Information from ESET Smart Security, version of virus >> > signature database 5164 (20100601) __________ >> > >> > The message was checked by ESET Smart Security. >> > >> > http://www.eset.com >> > >> > >> > >> >> __________ Information from ESET Smart Security, version of virus >> signature database 5164 (20100601) __________ >> >> The message was checked by ESET Smart Security. >> >> http://www.eset.com >> >> >> > > __________ Information from ESET Smart Security, version of virus > signature database 5165 (20100602) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > __________ Information from ESET Smart Security, version of virus signature database 5165 (20100602) __________ The message was checked by ESET Smart Security. http://www.eset.com
|
Pages: 1 Prev: Pre-configuring Pivot table "views" Next: Remove Spaces |