Charts and Dynamic Ranges

[EliseFreedman]

Hi there

I am trying to build a chart using a dynamic range

The range is on one spreadsheet and starts in cell D3. It is as follows

Slips trips & falls 4
Work at height 3
Fire risk 2
Hot work 1
Metal removal 1
Fuel distribution 1

The table goes down to cell E16. However there is actually only data down to cell E9. I am trying to create the chart so that it updates if data is added to the empty rows in the table.

I created a dynamic range with the following formula =OFFSET('Breakdown by Month'!$D$3,0,0,COUNTA('Breakdown by Month'!$D3:$D16),2). The data is all on the breakdown by month spreadsheet then the charts are on a separate spreadsheet. When I try to change the chart to reference the dynamic range, I get no data.

Not sure what i am doing wrong

 

[GlennUK]

What steps do you take to change the chart to reference the dynamic range? ( it might be that you are missing something like Sheetname!Definedname )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[EliseFreedman]

I have deleted and recreated one of my charts and it seems to have data in it now. However It misses out the last 2 rows of data e.g row 9

 

[EliseFreedman]

It also doesnt expand when i put in another row of data

 

[AnotherHiggins]

For help on the charting piece, see here:

http://office.microsoft.com/en-us/excel/HA011098011033.aspx

And I suspect you are having trouble with the Named Range, too. If you click on the Refers To field in Insert > Names > Define, it will put "marching ants" around the corresponding range. It looks to me like the fourth argument in your OFFSET (the argument for height) should be changed from
[tab]COUNTA('Breakdown by Month'!$D3:$D16)
to
[tab]COUNTA('Breakdown by Month'!$[red]E[/red]3:$[red]E[/red]16)
so you're only counting how many items have a count beside them. You can actually just refer to the entire column if there's a chance the table will ever grow. As in:
[tab]COUNTA('Breakdown by Month'!$[red]E[/red]:$[red]E[/red])

Keep in mind that this method will require that you sort the table descending on count.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlennUK]

Have you got Calculation mode as Automatic?


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[EliseFreedman]

=OFFSET('Breakdown by Month'!$D$3,0,0,COUNTA('Breakdown by Month'!$E3:$E16),2)

I have changed the formula to column E as suggested. However I am now getting #value!

 

[AnotherHiggins]

-> I am now getting #value!
Where?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Backing up, you need to use two Named Ranges:
[tab]- One for the X-Axis
[tab]- One for each data series

Let's say your source table looks like this in D2:E16 (I'm putting in headers for the columns in row 2):
[tt]
Thing Count
Slips trips & falls 4
Work at height 3
Fire risk 2
Hot work 1
Metal removal 1
Fuel distribution 1
Thing 1
Thing 2
Thing 3
Thing 4
Thing 5
Thing 6
Thing 7
Thing 8
[/tt]

Go to Insert > Name > Define and create a new name, let's say rngItems. In the Refers To field, type in:
[tab]=offset('Breakdown by Month'!$A$1, 2, 3, counta('Breakdown by Month'!$E:$E) - 1, 1)

Create another name, let's call it rngData. This is the formula for it:
[tab]=offset('Breakdown by Month'!$A$1, 2, 4, counta('Breakdown by Month'!$E:$E) - 1, 1)

Create a chart as normal. Click on the data series. In the formula bar, you should see something like this:

[tab]=SERIES('Breakdown by Month'!$E$2, 'Breakdown by Month'!$D$3:$D$16, 'Breakdown by Month'!$E$3:$E$16, 1)

The arguments you see are as follows:
[tab]=SERIES(Series Name, Category (X Axis) Labels, Values, Series Order)

You want to replace the Category and Values sections.

So in the formula bar, edit what you see to look like this:
[tab][COLOR=blue white]=SERIES('Breakdown by Month'!$E$2, 'Breakdown by Month'!rngItems, 'Breakdown by Month'!rngData, 1)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.