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

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

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

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