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?

Hi

You can have dynamic ranges as chart series sources.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"PH NEWS" wrote in message
...
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?

I don't understand what "dynamic ranges" are could you possibly explain
further?
"Arvi Laanemets" wrote in message
...
Hi

You can have dynamic ranges as chart series sources.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"PH NEWS" wrote in message
...
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?

Hi


"PH NEWS" wrote in message
...
I don't understand what "dynamic ranges" are could you possibly explain
further?

To define a named range, activate from manu InsertNameDefine.
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

thank you very much
"Arvi Laanemets" wrote in message
...
Hi


"PH NEWS" wrote in message
...
I don't understand what "dynamic ranges" are could you possibly explain
further?

To define a named range, activate from manu InsertNameDefine.
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

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
--------------------------------

"Arvi Laanemets" wrote:

Hi


"PH NEWS" wrote in message
...
I don't understand what "dynamic ranges" are could you possibly explain
further?

To define a named range, activate from manu InsertNameDefine.
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

I found it :))

http://www.tushar-mehta.com/excel/ne...tml#LastSoMany




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

"Andreas" wrote:

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
--------------------------------

"Arvi Laanemets" wrote:

Hi


"PH NEWS" wrote in message
...
I don't understand what "dynamic ranges" are could you possibly explain
further?

To define a named range, activate from manu InsertNameDefine.
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