Graphs with different ranges (dynamic range graph)

[Skybeau]

Hi guys,

I've got a problem with a graph I'm trying to prepare. The data range isn't always the same number. EG sometimes I might have 11 rows to be graphed (see below) and other times it might be 3 rows.

11 rows 3 rows

BONDI ------------- PERTH
HORNSBY ------------ ADELAIDE
CHATSWOOD ------- SYDNEY
ERINA
TUGGERAH
LAKE HAVEN
BATEAU BAY
MANLY
WARRINGAH MALL
CASTLE HILL
CENTRAL SHORE ADMIN

I've tried to do a graph for the entire range, but when I do that it will come up empty results if the whole range doesn't contain data. Is there any way I can code in VBA or through use of a formula to get the graph to chart only cells that have names in it ?

[asipoy]

Set the chart range to a named ranged (using name manager). In the name range use the OFFSET formula to select only the data which has values.

Do let me know if you have any questions or problems. I have tried this many times.