Variable chart input 1

[jnix]

I'm making a bar chart from an excel table that has 35 rows. The number of rows that has data in them varies; however, the empty rows are always at the end of the table. Is there a way to construct a chart that will only pick up the rows that have data in them?
Thanks,
jnix

 

[xlbo]

base the chart on a dynamic range name.
To set up a dynamic range name, follow Insert>Name>Define
Type a range name like "ChtData" and instead of the normal cell reference enter
=OFFSET('SheetName'!$A$1,,,counta('Sheetname'!A:A),2)

This assumes the chart data starts in A1 and has 2 columns. chnage the 2 to however many columns of data there are. This will create a range that expands and contracts as you add or remove data. Instead of using the normal cell references for the chart data, use
=ChtData
to reference the range.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[jnix]

xlbo:
It doesn't work for me. Could I send you my small table and let you show me how to do it?
Thanks,
jnix

 

[xlbo]

geoff dot barraclough at punchpubs dot co dot uk

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

This should be resolved now - Had to create seperate series names with slight amendments to series formulae needed due to extraneous data.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[klmorgan]

I used the ofset approach for some graphs and after playing with it I found that I could build the range in two cells, name the cells as a range and then set the graph range to the named range.

This way people can choose the range of data they want graphed and the graph shrinks or expands according to the range selected.

Say the worksheet is called Test

ie. Define the data range in two cells

Cell D10 Contains a1
Cell D11 Contains a20

Then Name the range d10:d11 MyRange


In the Graph Enter in the Values box ='Test.xls'!Myrange

The graph will then follow the range contained in the Range Myrange.

So if you change contents of D11 to a25 the range will increase

If you change d11 to a18 it will decrease

If you change d10 from a1 to a10 it will decrease

Likewise if you set the x range to variables you can set that to range according to a named range.

I have seven graphs in a workbook that uses this and users can choose the range (in my case dates) and all the graphs range automatically to their choice.

I have six workbooks one for each Production line that each have seven graphs that people use on a regular basis

I use a vlookup table that correlates date against cell that populates the ranges

Regards

Keith