macro for graphs

[BNT1 via OfficeKB.com]

Hi

I have seen a sheet in a workbook that just contains buttons to run macro for
graphs. there we plenty and doubt if they were painstakenly recorded on by
one and then assigned a button.

Can anyone point me in the right direction if its possible to short cut, by
just changing ranges. All the graphs will be the same format


Thank in advance

Regards

Brian

 

[Guest]

Hi,

If I understand your question you would like to change the chart by pressing
a button on the spreadsheet.

If this is what you want it doesn't require a macro. Range name your
different data ranges for the diffierent charts. For example A2:B10 would be
California, C210 would be Washington data.

Set up one data area for charting. In this area create formulas like:
=INDEX(INDIRECT($I$1),ROW(A1),COLUMN(A1))

In cell I1 add a data validation dropdown with the list option selected.
Have the list be a list of the range names of each of your data areas. Copy
this formula down as many rows as your data extends and over for as many
columns. In my example I copied it down 9 rows and over for 2 columns
because that is how large the data area A2:B10 is. When I pick a state from
the Data Validation drop down the chart area updates and the chart plotted
from that are changes.

 

[BNT1 via OfficeKB.com]

Thanks Shane, I have not egnored you reply, gust been trying to adapt my data
format to the explanation. I tried the define name approach, but when it come
to getting the graph for data that was not directly underneath each other, as
in Delivered + Late Deliveries, it didnt work
Let me explain

I have the folowing data

Wk1,wk2,w3,etc in cells b2,c2,d2
"Delivered " in A3
"Forecast " in a4
"Released" in A5
"Late Deliveries" in A6
the area b3:d6 is filled with values.

What I want to do, is allow the operator to choose the approriate wk value in
a graph form, for say Delivered vs Forecast, or Delivered vs. Late Deliveries.


Any help/direction appriciated?

regards

Brian

Hi,

If I understand your question you would like to change the chart by pressing
a button on the spreadsheet.

If this is what you want it doesn't require a macro. Range name your
different data ranges for the diffierent charts. For example A2:B10 would be
California, C210 would be Washington data.

Set up one data area for charting. In this area create formulas like:
=INDEX(INDIRECT($I$1),ROW(A1),COLUMN(A1))

In cell I1 add a data validation dropdown with the list option selected.
Have the list be a list of the range names of each of your data areas. Copy
this formula down as many rows as your data extends and over for as many
columns. In my example I copied it down 9 rows and over for 2 columns
because that is how large the data area A2:B10 is. When I pick a state from
the Data Validation drop down the chart area updates and the chart plotted
from that are changes.

Hi

[quoted text clipped - 10 lines]

Brian

 

[Jon Peltier]

Something like this?

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Thanks Shane, I have not egnored you reply, gust been trying to adapt my
data
format to the explanation. I tried the define name approach, but when it
come
to getting the graph for data that was not directly underneath each other,
as
in Delivered + Late Deliveries, it didnt work
Let me explain

I have the folowing data

Wk1,wk2,w3,etc in cells b2,c2,d2
"Delivered " in A3
"Forecast " in a4
"Released" in A5
"Late Deliveries" in A6
the area b3:d6 is filled with values.

What I want to do, is allow the operator to choose the approriate wk value
in
a graph form, for say Delivered vs Forecast, or Delivered vs. Late
Deliveries.


Any help/direction appriciated?

regards

Brian

Hi,

If I understand your question you would like to change the chart by
pressing
a button on the spreadsheet.

If this is what you want it doesn't require a macro. Range name your
different data ranges for the diffierent charts. For example A2:B10 would
be
California, C210 would be Washington data.

Set up one data area for charting. In this area create formulas like:
=INDEX(INDIRECT($I$1),ROW(A1),COLUMN(A1))

In cell I1 add a data validation dropdown with the list option selected.
Have the list be a list of the range names of each of your data areas.
Copy
this formula down as many rows as your data extends and over for as many
columns. In my example I copied it down 9 rows and over for 2 columns
because that is how large the data area A2:B10 is. When I pick a state
from
the Data Validation drop down the chart area updates and the chart plotted
from that are changes.

Hi

[quoted text clipped - 10 lines]

Brian

 

[BNT1 via OfficeKB.com]

Hi jon

Yes, have previously visited your site and have duplicated your example,
however, don't know how to show adjust your settings to unable graph to show
two bars per week

week numbers running along the row (row 5)
Forecast,Releases, Delivery, running down in column A

wishing the operator to select select 4 weeks of data, showing Forecast
verses Released. Which should show two vertical bars per week?

I thought Shane's approach was going to work, but could not adapt formula to
my layout of data

Appriciate you patience

regards

Something like this?

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Thanks Shane, I have not egnored you reply, gust been trying to adapt my
data

[quoted text clipped - 54 lines]

 

[Jon Peltier]

I show how to use sliders to adjust a date range here:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

If the two series are always Forecast and Release, you don't need any other
controls.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Hi jon

Yes, have previously visited your site and have duplicated your example,
however, don't know how to show adjust your settings to unable graph to
show
two bars per week

week numbers running along the row (row 5)
Forecast,Releases, Delivery, running down in column A

wishing the operator to select select 4 weeks of data, showing Forecast
verses Released. Which should show two vertical bars per week?

I thought Shane's approach was going to work, but could not adapt formula
to
my layout of data

Appriciate you patience

regards

Something like this?

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Thanks Shane, I have not egnored you reply, gust been trying to adapt my
data

[quoted text clipped - 54 lines]

Brian

 

[BNT1 via OfficeKB.com]

Thanks Jon, this is just what i am looking for and would be brill if i can
master it.

I have followed your example to the letter three times now, and cannot get it
to work,
When building graph, stage two, when I enter in the Value =data!chartfirma
and then click add, a error box comes up showing "Your formula contains an
invalid external reference to a worksheet", when I enter the " " marks before
and after the formula, it accepts it, however, when moving on the the next
add chart firm, the value box in the previous one i have entered changes to
={0}. This continues, for all firms and the result is nodata on the graph

Any ideas? Is everything case sensative?

regards

I show how to use sliders to adjust a date range here:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

If the two series are always Forecast and Release, you don't need any other
controls.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Hi jon

[quoted text clipped - 33 lines]

 

[Jon Peltier]

I just walked through the instructions to make sure I hadn't left anything
out. I had no issues, so the written steps seem okay. Things to check:

Sheet is named Data (not case sensitive, Excel corrects it).

Ranges ChartFirmA etc. exist (also not case sensitive).
Go to Insert menu > Names > Define. Are the names listed? Click in the
Refers To box of each: is the correct range highlighted in the worksheet?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Thanks Jon, this is just what i am looking for and would be brill if i can
master it.

I have followed your example to the letter three times now, and cannot get
it
to work,
When building graph, stage two, when I enter in the Value =data!chartfirma
and then click add, a error box comes up showing "Your formula contains an
invalid external reference to a worksheet", when I enter the " " marks
before
and after the formula, it accepts it, however, when moving on the the next
add chart firm, the value box in the previous one i have entered changes
to
={0}. This continues, for all firms and the result is nodata on the graph

Any ideas? Is everything case sensative?

regards

I show how to use sliders to adjust a date range here:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

If the two series are always Forecast and Release, you don't need any
other
controls.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Hi jon

[quoted text clipped - 33 lines]

Brian

 

[BNT1 via OfficeKB.com]

Hi Jon

Have been throgh this again and again, until Presto ! got it. Problem was
the same typo error, - learnt a lesson here !

Could i ask one last question, my headings go across the page, wk1,wk2,wk3
etc. Down the column reads, Forecast,Releases,Deliveries,Lates,etc. Is it
possible to adapt the chart to enable the operator to select the range of wks
and show the data on the chart for Forecast and Releases only?

Regards

I just walked through the instructions to make sure I hadn't left anything
out. I had no issues, so the written steps seem okay. Things to check:

Sheet is named Data (not case sensitive, Excel corrects it).

Ranges ChartFirmA etc. exist (also not case sensitive).
Go to Insert menu > Names > Define. Are the names listed? Click in the
Refers To box of each: is the correct range highlighted in the worksheet?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Thanks Jon, this is just what i am looking for and would be brill if i can
master it.

[quoted text clipped - 35 lines]

 

[Jon Peltier]

Problem was the same typo error

Doh!

wk1,wk2,wk3 go across the page

You mean you want it by row rather than by column? This is done by altering
the OFFSET arguments. Check this out in the help files or look at the
example in more depth to understand how Offset identifies a particular
range.

and show the data on the chart for Forecast and Releases only?

Just have those two series in the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Hi Jon

Have been throgh this again and again, until Presto ! got it. Problem was
the same typo error, - learnt a lesson here !

Could i ask one last question, my headings go across the page, wk1,wk2,wk3
etc. Down the column reads, Forecast,Releases,Deliveries,Lates,etc. Is
it
possible to adapt the chart to enable the operator to select the range of
wks
and show the data on the chart for Forecast and Releases only?

Regards

I just walked through the instructions to make sure I hadn't left anything
out. I had no issues, so the written steps seem okay. Things to check:

Sheet is named Data (not case sensitive, Excel corrects it).

Ranges ChartFirmA etc. exist (also not case sensitive).
Go to Insert menu > Names > Define. Are the names listed? Click in the
Refers To box of each: is the correct range highlighted in the worksheet?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Thanks Jon, this is just what i am looking for and would be brill if i
can
master it.

[quoted text clipped - 35 lines]

Brian