I am working on a year-end report for my clients that shows them information
associated with their accounts. Most of the information on each client's
reports relates to the current year and is easy to retreive from the tables
that store all the clients' information. However, there is historical
information for each client that is more difficult to report.

I want to display a graph for each client showing the year-to-year change in
"value" for them. But since each client has a different number of data
points, it is proving hard for me to format the graphs properly.

I have built a query to trap the pertinent data for each client, whether it
is 1 record or 20 records. But I cannot figure out how to have a variable
range named in the graph's parameters based on how many rows the particlar
client might have. I tried naming a "super-set" of records...that is I
hightlighted a range including 40 rows, which will always include all of the
client records, whether there is 1 or 15..but the blank rows actually get
included as data points in the graph.

One possible solution path. In anticipation of this sort of problem, I
included a START DATE and END DATE variable for each client..but I cannot
seem to find a way to use those variables in naming my range. These ended up
being used in the query...

Any ideas would be appreciated.

regards,
Tom

I have found out about OFFSET...and am trying to use it. I have some
questions, as it is not working as I expect.

Can I replace my SERIES expression with an OFFSET expression, for each
series my graph uses? I am using a 3-D area chart with 2 X-Axis series...and
when I replace the SERIES expression with OFFSET to define a differet, it
does not work...

I cannot find any description for the parameters for a SERIES expression,
which would help me understand if OFFSET replaces it in part or in full.
Using the Wizard has its drawbacks...

I cannot find the Y-Axis expression in order to amend it, as the examples I
used referered to. I suspect that is a condiation of the libne graph used in
teh example, and I will not have such a beast in my 3-D area graph...or do I?
How can I find it?
"Tom Mackay" wrote:

I am working on a year-end report for my clients that shows them information
associated with their accounts. Most of the information on each client's
reports relates to the current year and is easy to retreive from the tables
that store all the clients' information. However, there is historical
information for each client that is more difficult to report.

I want to display a graph for each client showing the year-to-year change in
"value" for them. But since each client has a different number of data
points, it is proving hard for me to format the graphs properly.

I have built a query to trap the pertinent data for each client, whether it
is 1 record or 20 records. But I cannot figure out how to have a variable
range named in the graph's parameters based on how many rows the particlar
client might have. I tried naming a "super-set" of records...that is I
hightlighted a range including 40 rows, which will always include all of the
client records, whether there is 1 or 15..but the blank rows actually get
included as data points in the graph.

One possible solution path. In anticipation of this sort of problem, I
included a START DATE and END DATE variable for each client..but I cannot
seem to find a way to use those variables in naming my range. These ended up
being used in the query...

Any ideas would be appreciated.

regards,
Tom

Hi,

The offset formula is used to build a named range. The named range is
then used within the charts series formula.

See Jon Peltier's pages for information.

Series Formula explanation.
http://peltiertech.com/Excel/ChartsH...esFormula.html

And for use of the offset.
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

Tom Mackay wrote:
I have found out about OFFSET...and am trying to use it. I have some
questions, as it is not working as I expect.

Can I replace my SERIES expression with an OFFSET expression, for each
series my graph uses? I am using a 3-D area chart with 2 X-Axis series...and
when I replace the SERIES expression with OFFSET to define a differet, it
does not work...

I cannot find any description for the parameters for a SERIES expression,
which would help me understand if OFFSET replaces it in part or in full.
Using the Wizard has its drawbacks...

I cannot find the Y-Axis expression in order to amend it, as the examples I
used referered to. I suspect that is a condiation of the libne graph used in
teh example, and I will not have such a beast in my 3-D area graph...or do I?
How can I find it?
"Tom Mackay" wrote:


I am working on a year-end report for my clients that shows them information
associated with their accounts. Most of the information on each client's
reports relates to the current year and is easy to retreive from the tables
that store all the clients' information. However, there is historical
information for each client that is more difficult to report.

I want to display a graph for each client showing the year-to-year change in
"value" for them. But since each client has a different number of data
points, it is proving hard for me to format the graphs properly.

I have built a query to trap the pertinent data for each client, whether it
is 1 record or 20 records. But I cannot figure out how to have a variable
range named in the graph's parameters based on how many rows the particlar
client might have. I tried naming a "super-set" of records...that is I
hightlighted a range including 40 rows, which will always include all of the
client records, whether there is 1 or 15..but the blank rows actually get
included as data points in the graph.

One possible solution path. In anticipation of this sort of problem, I
included a START DATE and END DATE variable for each client..but I cannot
seem to find a way to use those variables in naming my range. These ended up
being used in the query...

Any ideas would be appreciated.

regards,
Tom

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Thank you Andy. I managed to work out something using OFFSET...I still have
a lot of things to get through but at least I understand this function now in
context of graphing.

I must say, I am pretty surprised that the Excel online help and all the
textbooks I refered to never said a thing about the paramaters of
SERIES...which is what I needed...is this too advanced?

One more question...if you are interested. I have now successfully created
a one page report, suitable for reporting the info associated with one
client. But I will usually want to produce 500 reports for 500 different
clients at once, with one simple action. What tecniques can I explore to try
to do this? The only thing I can think of is cut and pasting the content of
my report either (a) into 500 different spreadsheets or (b) into one sheet of
a single spreadsheet 500 times. But neither of these seems to be very
friendly...the former is bad because I then have to open 500 different
spreadsheets...and the latter is bad because I don't think i can keep the
cell referencing straight.

"Andy Pope" wrote:

Hi,

The offset formula is used to build a named range. The named range is
then used within the charts series formula.

See Jon Peltier's pages for information.

Series Formula explanation.
http://peltiertech.com/Excel/ChartsH...esFormula.html

And for use of the offset.
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

Tom Mackay wrote:
I have found out about OFFSET...and am trying to use it. I have some
questions, as it is not working as I expect.

Can I replace my SERIES expression with an OFFSET expression, for each
series my graph uses? I am using a 3-D area chart with 2 X-Axis series...and
when I replace the SERIES expression with OFFSET to define a differet, it
does not work...

I cannot find any description for the parameters for a SERIES expression,
which would help me understand if OFFSET replaces it in part or in full.
Using the Wizard has its drawbacks...

I cannot find the Y-Axis expression in order to amend it, as the examples I
used referered to. I suspect that is a condiation of the libne graph used in
teh example, and I will not have such a beast in my 3-D area graph...or do I?
How can I find it?
"Tom Mackay" wrote:


I am working on a year-end report for my clients that shows them information
associated with their accounts. Most of the information on each client's
reports relates to the current year and is easy to retreive from the tables
that store all the clients' information. However, there is historical
information for each client that is more difficult to report.

I want to display a graph for each client showing the year-to-year change in
"value" for them. But since each client has a different number of data
points, it is proving hard for me to format the graphs properly.

I have built a query to trap the pertinent data for each client, whether it
is 1 record or 20 records. But I cannot figure out how to have a variable
range named in the graph's parameters based on how many rows the particlar
client might have. I tried naming a "super-set" of records...that is I
hightlighted a range including 40 rows, which will always include all of the
client records, whether there is 1 or 15..but the blank rows actually get
included as data points in the graph.

One possible solution path. In anticipation of this sort of problem, I
included a START DATE and END DATE variable for each client..but I cannot
seem to find a way to use those variables in naming my range. These ended up
being used in the query...

Any ideas would be appreciated.

regards,
Tom

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Always nice to post your final successful result for the archives.

On the reports, without a lot more detail it is impossible to tell but
sometimes
="blah blah "& a17 & "blah blah"
works

--
Don Guillett
SalesAid Software

"Tom Mackay" wrote in message
...
Thank you Andy. I managed to work out something using OFFSET...I still
have
a lot of things to get through but at least I understand this function now
in
context of graphing.

I must say, I am pretty surprised that the Excel online help and all the
textbooks I refered to never said a thing about the paramaters of
SERIES...which is what I needed...is this too advanced?

One more question...if you are interested. I have now successfully
created
a one page report, suitable for reporting the info associated with one
client. But I will usually want to produce 500 reports for 500 different
clients at once, with one simple action. What tecniques can I explore to
try
to do this? The only thing I can think of is cut and pasting the content
of
my report either (a) into 500 different spreadsheets or (b) into one sheet
of
a single spreadsheet 500 times. But neither of these seems to be very
friendly...the former is bad because I then have to open 500 different
spreadsheets...and the latter is bad because I don't think i can keep the
cell referencing straight.

"Andy Pope" wrote:

Hi,

The offset formula is used to build a named range. The named range is
then used within the charts series formula.

See Jon Peltier's pages for information.

Series Formula explanation.
http://peltiertech.com/Excel/ChartsH...esFormula.html

And for use of the offset.
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

Tom Mackay wrote:
I have found out about OFFSET...and am trying to use it. I have some
questions, as it is not working as I expect.

Can I replace my SERIES expression with an OFFSET expression, for each
series my graph uses? I am using a 3-D area chart with 2 X-Axis
series...and
when I replace the SERIES expression with OFFSET to define a differet,
it
does not work...

I cannot find any description for the parameters for a SERIES
expression,
which would help me understand if OFFSET replaces it in part or in
full.
Using the Wizard has its drawbacks...

I cannot find the Y-Axis expression in order to amend it, as the
examples I
used referered to. I suspect that is a condiation of the libne graph
used in
teh example, and I will not have such a beast in my 3-D area graph...or
do I?
How can I find it?
"Tom Mackay" wrote:


I am working on a year-end report for my clients that shows them
information
associated with their accounts. Most of the information on each
client's
reports relates to the current year and is easy to retreive from the
tables
that store all the clients' information. However, there is historical
information for each client that is more difficult to report.

I want to display a graph for each client showing the year-to-year
change in
"value" for them. But since each client has a different number of data
points, it is proving hard for me to format the graphs properly.

I have built a query to trap the pertinent data for each client,
whether it
is 1 record or 20 records. But I cannot figure out how to have a
variable
range named in the graph's parameters based on how many rows the
particlar
client might have. I tried naming a "super-set" of records...that is I
hightlighted a range including 40 rows, which will always include all
of the
client records, whether there is 1 or 15..but the blank rows actually
get
included as data points in the graph.

One possible solution path. In anticipation of this sort of problem, I
included a START DATE and END DATE variable for each client..but I
cannot
seem to find a way to use those variables in naming my range. These
ended up
being used in the query...

Any ideas would be appreciated.

regards,
Tom

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info