excel charts, simple question

[PH NEWS]

Each month I produce a list of employees and the hours they have worked like
so,
Column A Column B
Bob Smith 25
Phil Jones 28

etc, etc. The number of employees changes each month but I don't want to
have to go through the chart building process each month and this is where
my problem occurs. If I select a large range, one I know is not going to be
exceeded by the amount of employees, for every cell that there isn't an
employee my chart shows a "0". So is there anyway I can get my chart to
concentrate on the applicable data and ignore the blank cells in my range?

 

[Arvi Laanemets]

Hi

You can have dynamic ranges as chart series sources.

 

[PH NEWS]

I don't understand what "dynamic ranges" are could you possibly explain
further?

 

[Arvi Laanemets]

Hi

I don't understand what "dynamic ranges" are could you possibly explain
further?

To define a named range, activate from manu Insert>Name>Define.
Easiest is to define a static name - you select the range, and name it (type
the name into Name field in toolbar, or into Name field in Define Name
window. In Refers To field of Define Name window you see the formula like
=Sheet1!$A$2:$A$4
When the name refers to more than 1 cell, it always returns a range. When
the name refers to single cell, then it may be interpreted as both range or
value - depending on call.

A step further is to define name dynamically, i.e. the range, the name
refers to (or returned value), depends on some condition (the number of
entries, the position of active cell, etc.). How to do it, is up to your
skill.

An example with your data. I assume, that your table is on sheet Sheet1,
names are in column A, hours are in column B, and A1:B1 are column headers -
actiual data start from row 2.

Define names
Names=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,1)
Hours=OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$A:$A)-1,1)

Select range A1:B3, and create a chart of column type
Right-click on chart, ans select Source Data - activate Series tab.

In 'Values' field, you see
Sheet1!$B$2:$B$3
Replace it with
Sheet1!Hours

In 'Categories (X) axis labels' field, you see
Sheet1!$A$2:$A$3
Replace it with
Sheet1!Names

Close Source Date window (press OK)

Now, when you add a new name into table, or delete some, the graph is
adjusting immediately.
NB! The way the name is defined assumes, that there never are any gaps
(empty rows) in table. When you add new names, add them to next row at
bottom. When you delete some entry, delete the entire row.


Arvi Laanemets

 

[PH NEWS]

thank you very much

Hi




To define a named range, activate from manu Insert>Name>Define.
Easiest is to define a static name - you select the range, and name it (type
the name into Name field in toolbar, or into Name field in Define Name
window. In Refers To field of Define Name window you see the formula like
=Sheet1!$A$2:$A$4
When the name refers to more than 1 cell, it always returns a range. When
the name refers to single cell, then it may be interpreted as both range or
value - depending on call.

A step further is to define name dynamically, i.e. the range, the name
refers to (or returned value), depends on some condition (the number of
entries, the position of active cell, etc.). How to do it, is up to your
skill.

An example with your data. I assume, that your table is on sheet Sheet1,
names are in column A, hours are in column B, and A1:B1 are column headers -
actiual data start from row 2.

Define names
Names=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,1)
Hours=OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$A:$A)-1,1)

Select range A1:B3, and create a chart of column type
Right-click on chart, ans select Source Data - activate Series tab.

In 'Values' field, you see
Sheet1!$B$2:$B$3
Replace it with
Sheet1!Hours

In 'Categories (X) axis labels' field, you see
Sheet1!$A$2:$A$3
Replace it with
Sheet1!Names

Close Source Date window (press OK)

Now, when you add a new name into table, or delete some, the graph is
adjusting immediately.
NB! The way the name is defined assumes, that there never are any gaps
(empty rows) in table. When you add new names, add them to next row at
bottom. When you delete some entry, delete the entire row.


Arvi Laanemets

 

[Guest]

Arvi:

I have a follow-up question about this:
How do I specify the range, if I only want to use the last 12 entries (last
12 rows counting from the bottom)?

Thanks,

Andreas
--------------------------------

 

[Guest]

I found it )

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html#LastSoMany




----------------------------------