Loose formula/value (when building graphs)

P

PvZ

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
 
R

Roger Govier

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 said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5164 (20100601) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
P

PvZ

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 said:
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 said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5164 (20100601) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...
 
R

Roger Govier

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 said:
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 said:
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 said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus
signature database 5164 (20100601) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus
signature database 5165 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5165 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reference(s) to worksheets for building graphs 4
Create two Data validation lists that are linked(Both change toget 2
If data present, then display graph. 2
Macro to Create Hundreds of Graphs 7
Automatic Macro to create 200 graphs 1
Advice on simple graphs with Excel 2010 4
Median Formula not working properly 2
Hide line and legend when data not present 0

Top