PDA

View Full Version : Solved: HOw to make a dynamic bar diagram?



maryam
04-30-2007, 05:00 AM
I want to have a dynamic bar diagram which shows the diagram once the worksheet is activate. the data are taken from two rows of the worksheet. How to make such a chart by coding?

JonPeltier
05-01-2007, 06:40 PM
You don't need VBA. Adjust the named ranges in this example to define horizontal, not vertical, ranges:

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

maryam
05-02-2007, 12:14 AM
this looks like what I am looking for. but I cannot do. when I define SheetEquipment!ChartsValue it gives the name is not valid. In OFFSET I cannot understand the inputs.

maryam
05-02-2007, 01:21 AM
please ignore my last email. I found out completely. Thank you for the links.

maryam
05-02-2007, 07:55 PM
Dear JohnPeltier,
Is it possible that the user can select the rows he wants to have a bar diagram for them? I mean for example there are two columns of data. We want to have a bar diagram of some of these data. for example 1st, 3rd,4th row. Is it possible to give this choice to the user?

JonPeltier
05-02-2007, 08:13 PM
You could put some values into another column, which isn't being charted, and filter on this column. By default, a chart does not show hidden data, so filtering out the 2nd row would give you this effect.

maryam
05-02-2007, 09:15 PM
the rows we want to filter out ( or the rows we want for bar diagram) are not fixed. I want to give the flexibility to the user to select the rows.
You mean I should put the data which are selected in seprate columns and then select those columns for the chart?
or making the data which are not selected hidden and then the chart will show other data?

maryam
05-02-2007, 09:16 PM
how to make the data hidden?

maryam
05-02-2007, 10:18 PM
I can hide the whole column or row, but is it possible to hide cells?

lucas
05-02-2007, 10:37 PM
Make their fonts white....

maryam
05-02-2007, 10:49 PM
haha! I need to hide them so that they will not be charted.

JonPeltier
05-03-2007, 03:41 AM
Make the data table into an Autofilter, or if you're using Excel 2003, into a List (see Data menu for either). Autofilters and Lists (and Tables in 2007) allow you to easily select whether to show or hide rows based on criteria you can define.

maryam
05-03-2007, 07:18 AM
I use Excel 2003. I found list there, but I cannot understant.
How to combine this and dynamic chart?

I wanted a dynamic chart (with flexible number of columns) , means we have a chart which can be for 2 columns, 4colums or 10 columns for example.I made that using dynamic chart procedure.

Now I want the user to be able to select between the columns also and if we have 10 columns still user may not want column 4 to be charted.

Is it clear?

maryam
05-04-2007, 07:26 AM
I define my data in a list, but how to show or hide?
from chart, data source, in datarange I can select the cells I want, but I want the user only to select the cells from worksheet and then those data will be charted in a dynamic chart.
How to make this post unsolved?

JonPeltier
05-04-2007, 08:04 AM
I think the only way you could do this without building something in VBA is to use an AutoFilter that the user could manipulate. This requires data in columns, so the AutoFilter would hide particular rows.

maryam
05-04-2007, 08:19 PM
There is no special rule for filtering, I just want to let the user select between the columns based on his interest.

JonPeltier
05-05-2007, 06:14 AM
Maybe you could use the checkbox approach here:

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

maryam
05-19-2007, 05:33 AM
Dear JonPeltier,
I am using this that u sent me in the 2nd post.
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
It works but when I save the sheet and later I want to open I get these messeges and it will not work in the new sheet:
A formula or sheet you want to move or copy contains the name 'chartlabels', which already exits on the destination worksheet. do you want to use this version of name?
to use the name as defined in the destination sheet, click yes
to rename click no and enter a new name in the name dialog box
please
:help

JonPeltier
06-28-2007, 07:51 AM
When you define a name, it is valid for the entire workbook. Adding a sheet that also has this name to this workbook causes Excel to ask whether you want to use the already defined name in the new sheet. You have a choice to change the names in the inserted sheet, which you should do, but the references in the chart will point to the already existing name in the workbook, because Excel doesn't go looking in the new sheet for links to the name so they can be updated.

You can define a name for its parent sheet only. When you name it, insert the sheet name first:

'My Sheet1'!MyName

Now the name will not have this conflict when inserted into a workbook that has a matching name.

However, when you copy a sheet containing a chart that uses a worksheet Name (as opposed to a workbook Name), the links to the Name are broken. To avoid this, move the sheet instead of copying it. To retain the original sheet and chart, move it to a new workbook and save the workbook. Move the sheet to the old workbook (which will close the new workbook because there are no other sheets), reopen the new workbook, move the sheet again, and repeat as necessary.

If this seems complicated (and it is), practice on some dummy workbooks and charts, so you don't hose your work.