| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loose formula/value (when building graphs)
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Loose formula/value (when building graphs)
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 tabView 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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Loose formula/value (when building graphs)
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 tabView 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Loose formula/value (when building graphs)
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" wrote in message ... 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 tabView 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reference(s) to worksheets for building graphs | Excel Worksheet Functions | |||
| Loose formatting when printing more than one sheet | Excel Discussion (Misc queries) | |||
| Formula Building in Excel 2007 | Excel Discussion (Misc queries) | |||
| Why do I loose color in cells with this string? | Excel Worksheet Functions | |||
| how to loose all numbers after decimal (not rounding) | Excel Worksheet Functions | |||
Linear Mode
