software

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 quot;0quot;. 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.laanemetslt;atgt;tarkon.ee )
quot;PH NEWSquot; gt; wrote in message
...
gt; Each month I produce a list of employees and the hours they have worked
gt; like
gt; so,
gt; Column A Column B
gt; Bob Smith 25
gt; Phil Jones 28
gt;
gt; etc, etc. The number of employees changes each month but I don't want to
gt; have to go through the chart building process each month and this is where
gt; my problem occurs. If I select a large range, one I know is not going to
gt; be
gt; exceeded by the amount of employees, for every cell that there isn't an
gt; employee my chart shows a quot;0quot;. So is there anyway I can get my chart to
gt; concentrate on the applicable data and ignore the blank cells in my range?
gt;
gt;
I don't understand what quot;dynamic rangesquot; are could you possibly explain
further?
quot;Arvi Laanemetsquot; gt; wrote in message
...
gt; Hi
gt;
gt; You can have dynamic ranges as chart series sources.
gt;
gt;
gt; --
gt; Arvi Laanemets
gt; ( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
gt;
gt;
gt;
gt; quot;PH NEWSquot; gt; wrote in message
gt; ...
gt; gt; Each month I produce a list of employees and the hours they have worked
gt; gt; like
gt; gt; so,
gt; gt; Column A Column B
gt; gt; Bob Smith 25
gt; gt; Phil Jones 28
gt; gt;
gt; gt; etc, etc. The number of employees changes each month but I don't want to
gt; gt; have to go through the chart building process each month and this is
where
gt; gt; my problem occurs. If I select a large range, one I know is not going to
gt; gt; be
gt; gt; exceeded by the amount of employees, for every cell that there isn't an
gt; gt; employee my chart shows a quot;0quot;. So is there anyway I can get my chart to
gt; gt; concentrate on the applicable data and ignore the blank cells in my
range?
gt; gt;
gt; gt;
gt;
gt;
Hiquot;PH NEWSquot; gt; wrote in message
...
gt; I don't understand what quot;dynamic rangesquot; are could you possibly explain
gt; further?

To define a named range, activate from manu Insertgt;Namegt;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
thank you very much
quot;Arvi Laanemetsquot; gt; wrote in message
...
gt; Hi
gt;
gt;
gt; quot;PH NEWSquot; gt; wrote in message
gt; ...
gt; gt; I don't understand what quot;dynamic rangesquot; are could you possibly explain
gt; gt; further?
gt;
gt; To define a named range, activate from manu Insertgt;Namegt;Define.
gt; Easiest is to define a static name - you select the range, and name it
(type
gt; the name into Name field in toolbar, or into Name field in Define Name
gt; window. In Refers To field of Define Name window you see the formula like
gt; =Sheet1!$A$2:$A$4
gt; When the name refers to more than 1 cell, it always returns a range. When
gt; the name refers to single cell, then it may be interpreted as both range
or
gt; value - depending on call.
gt;
gt; A step further is to define name dynamically, i.e. the range, the name
gt; refers to (or returned value), depends on some condition (the number of
gt; entries, the position of active cell, etc.). How to do it, is up to your
gt; skill.
gt;
gt; An example with your data. I assume, that your table is on sheet Sheet1,
gt; names are in column A, hours are in column B, and A1:B1 are column
headers -
gt; actiual data start from row 2.
gt;
gt; Define names
gt; Names=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,1)
gt; Hours=OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$A:$A)-1,1)
gt;
gt; Select range A1:B3, and create a chart of column type
gt; Right-click on chart, ans select Source Data - activate Series tab.
gt;
gt; In 'Values' field, you see
gt; Sheet1!$B$2:$B$3
gt; Replace it with
gt; Sheet1!Hours
gt;
gt; In 'Categories (X) axis labels' field, you see
gt; Sheet1!$A$2:$A$3
gt; Replace it with
gt; Sheet1!Names
gt;
gt; Close Source Date window (press OK)
gt;
gt; Now, when you add a new name into table, or delete some, the graph is
gt; adjusting immediately.
gt; NB! The way the name is defined assumes, that there never are any gaps
gt; (empty rows) in table. When you add new names, add them to next row at
gt; bottom. When you delete some entry, delete the entire row.
gt;
gt;
gt; Arvi Laanemets
gt;
gt;
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
--------------------------------

quot;Arvi Laanemetsquot; wrote:

gt; Hi
gt;
gt;
gt; quot;PH NEWSquot; gt; wrote in message
gt; ...
gt; gt; I don't understand what quot;dynamic rangesquot; are could you possibly explain
gt; gt; further?
gt;
gt; To define a named range, activate from manu Insertgt;Namegt;Define.
gt; Easiest is to define a static name - you select the range, and name it (type
gt; the name into Name field in toolbar, or into Name field in Define Name
gt; window. In Refers To field of Define Name window you see the formula like
gt; =Sheet1!$A$2:$A$4
gt; When the name refers to more than 1 cell, it always returns a range. When
gt; the name refers to single cell, then it may be interpreted as both range or
gt; value - depending on call.
gt;
gt; A step further is to define name dynamically, i.e. the range, the name
gt; refers to (or returned value), depends on some condition (the number of
gt; entries, the position of active cell, etc.). How to do it, is up to your
gt; skill.
gt;
gt; An example with your data. I assume, that your table is on sheet Sheet1,
gt; names are in column A, hours are in column B, and A1:B1 are column headers -
gt; actiual data start from row 2.
gt;
gt; Define names
gt; Names=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,1)
gt; Hours=OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$A:$A)-1,1)
gt;
gt; Select range A1:B3, and create a chart of column type
gt; Right-click on chart, ans select Source Data - activate Series tab.
gt;
gt; In 'Values' field, you see
gt; Sheet1!$B$2:$B$3
gt; Replace it with
gt; Sheet1!Hours
gt;
gt; In 'Categories (X) axis labels' field, you see
gt; Sheet1!$A$2:$A$3
gt; Replace it with
gt; Sheet1!Names
gt;
gt; Close Source Date window (press OK)
gt;
gt; Now, when you add a new name into table, or delete some, the graph is
gt; adjusting immediately.
gt; NB! The way the name is defined assumes, that there never are any gaps
gt; (empty rows) in table. When you add new names, add them to next row at
gt; bottom. When you delete some entry, delete the entire row.
gt;
gt;
gt; Arvi Laanemets
gt;
gt;
gt;

I found it )

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

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

quot;Andreasquot; wrote:

gt; Arvi:
gt;
gt; I have a follow-up question about this:
gt; How do I specify the range, if I only want to use the last 12 entries (last
gt; 12 rows counting from the bottom)?
gt;
gt; Thanks,
gt;
gt; Andreas
gt; --------------------------------
gt;
gt; quot;Arvi Laanemetsquot; wrote:
gt;
gt; gt; Hi
gt; gt;
gt; gt;
gt; gt; quot;PH NEWSquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I don't understand what quot;dynamic rangesquot; are could you possibly explain
gt; gt; gt; further?
gt; gt;
gt; gt; To define a named range, activate from manu Insertgt;Namegt;Define.
gt; gt; Easiest is to define a static name - you select the range, and name it (type
gt; gt; the name into Name field in toolbar, or into Name field in Define Name
gt; gt; window. In Refers To field of Define Name window you see the formula like
gt; gt; =Sheet1!$A$2:$A$4
gt; gt; When the name refers to more than 1 cell, it always returns a range. When
gt; gt; the name refers to single cell, then it may be interpreted as both range or
gt; gt; value - depending on call.
gt; gt;
gt; gt; A step further is to define name dynamically, i.e. the range, the name
gt; gt; refers to (or returned value), depends on some condition (the number of
gt; gt; entries, the position of active cell, etc.). How to do it, is up to your
gt; gt; skill.
gt; gt;
gt; gt; An example with your data. I assume, that your table is on sheet Sheet1,
gt; gt; names are in column A, hours are in column B, and A1:B1 are column headers -
gt; gt; actiual data start from row 2.
gt; gt;
gt; gt; Define names
gt; gt; Names=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,1)
gt; gt; Hours=OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$A:$A)-1,1)
gt; gt;
gt; gt; Select range A1:B3, and create a chart of column type
gt; gt; Right-click on chart, ans select Source Data - activate Series tab.
gt; gt;
gt; gt; In 'Values' field, you see
gt; gt; Sheet1!$B$2:$B$3
gt; gt; Replace it with
gt; gt; Sheet1!Hours
gt; gt;
gt; gt; In 'Categories (X) axis labels' field, you see
gt; gt; Sheet1!$A$2:$A$3
gt; gt; Replace it with
gt; gt; Sheet1!Names
gt; gt;
gt; gt; Close Source Date window (press OK)
gt; gt;
gt; gt; Now, when you add a new name into table, or delete some, the graph is
gt; gt; adjusting immediately.
gt; gt; NB! The way the name is defined assumes, that there never are any gaps
gt; gt; (empty rows) in table. When you add new names, add them to next row at
gt; gt; bottom. When you delete some entry, delete the entire row.
gt; gt;
gt; gt;
gt; gt; Arvi Laanemets
gt; gt;
gt; gt;
gt; gt;