Help Transpose, Chart Last 12 Month Dynamically
 

I'm trying to transpose a chart example on Jon Peltier's website. I have to
much information to use it like the example. Row 2 would be Month, Row 3
would be Value A, Row 4 would be Value B. I believe I would have to make
changes in Define Name
variables and also in Chart Series. I'm not successful at the moment.

TIA

Help Transpose, Chart Last 12 Month Dynamically
 

Hi,

There are hundreds of charts at Jon's site - what are we talking about?

This is a guess, off the top of my head

You have a defined name such as =OFFSET(A1,0,0,COUNTA(A1:A100))
and you want to go the other way
=OFFSET(A1,0,0,,COUNTA(A1:M1))

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Noreaster" wrote:

I'm trying to transpose a chart example on Jon Peltier's website. I have to
much information to use it like the example. Row 2 would be Month, Row 3
would be Value A, Row 4 would be Value B. I believe I would have to make
changes in Define Name
variables and also in Chart Series. I'm not successful at the moment.

TIA

Help Transpose, Chart Last 12 Month Dynamically
 

This is the one I'm trying to transpose,
http://peltiertech.com/Excel/Charts/DynamicLast12.html
to go from left to right, Not top to bottom

"ShaneDevenshire" wrote:

Hi,

There are hundreds of charts at Jon's site - what are we talking about?

This is a guess, off the top of my head

You have a defined name such as =OFFSET(A1,0,0,COUNTA(A1:A100))
and you want to go the other way
=OFFSET(A1,0,0,,COUNTA(A1:M1))

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Noreaster" wrote:

I'm trying to transpose a chart example on Jon Peltier's website. I have too
much information to use it like the example. Row 2 would be Month, Row 3
would be Value A, Row 4 would be Value B. I believe I would have to make
changes in Define Name
variables and also in Chart Series. I'm not successful at the moment.

TIA

Help Transpose, Chart Last 12 Month Dynamically
 

Hi,

It's just as I suggested you need to switch the last two arguments of the
OFFSET function. Something like

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

=OFFSET(Sheet1!$A$1,0,COUNTA(Sheet1!$1:$1)-1,1,-MIN(chtLen,COUNTA(Sheet1!$1:$1)-1))

and

=OFFSET(chtCats,1,0)

=OFFSET(chtCats,2,0)

The most important thing is to understand the OFFSET function so you can
adapt it to your needs. Take a look at the Excel Help for the details.

This is the best I can do without detail description of your data layout.

--
Thanks,
Shane Devenshire


"Noreaster" wrote:

This is the one I'm trying to transpose,
http://peltiertech.com/Excel/Charts/DynamicLast12.html
to go from left to right, Not top to bottom

"ShaneDevenshire" wrote:

Hi,

There are hundreds of charts at Jon's site - what are we talking about?

This is a guess, off the top of my head

You have a defined name such as =OFFSET(A1,0,0,COUNTA(A1:A100))
and you want to go the other way
=OFFSET(A1,0,0,,COUNTA(A1:M1))

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Noreaster" wrote:

I'm trying to transpose a chart example on Jon Peltier's website. I have too
much information to use it like the example. Row 2 would be Month, Row 3
would be Value A, Row 4 would be Value B. I believe I would have to make
changes in Define Name
variables and also in Chart Series. I'm not successful at the moment.

TIA

Help Transpose, Chart Last 12 Month Dynamically
 

That works for me, minor tweekage for my needs. :-)
Would it be better to have the charts on seperate sheets? I'm thinking it
would it easier to do like a batch printing of charts w/o the worksheet with
data. I have a "Data" sheet where the charts will be getting the data from.

I have about 70 charts to do with 3 variables in Define/Name . Is there an
easier way to input the Data in Define/Name or would each one have to be done
manually? I have figured what Name variables will be, so it wont be
confusing. The charts I can copy and edit the source.

How many Define/Names can Excell 2003 handle?

Thank you very much for your help.

"ShaneDevenshire" wrote:

Hi,

It's just as I suggested you need to switch the last two arguments of the
OFFSET function. Something like

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(chtLen,COUNTA(Sheet1!$A:$A)-1),1)

=OFFSET(Sheet1!$A$1,0,COUNTA(Sheet1!$1:$1)-1,1,-MIN(chtLen,COUNTA(Sheet1!$1:$1)-1))

and

=OFFSET(chtCats,1,0)

=OFFSET(chtCats,2,0)

The most important thing is to understand the OFFSET function so you can
adapt it to your needs. Take a look at the Excel Help for the details.

This is the best I can do without detail description of your data layout.

--
Thanks,
Shane Devenshire


"Noreaster" wrote:

This is the one I'm trying to transpose,
http://peltiertech.com/Excel/Charts/DynamicLast12.html
to go from left to right, Not top to bottom

"ShaneDevenshire" wrote:

Hi,

There are hundreds of charts at Jon's site - what are we talking about?

This is a guess, off the top of my head

You have a defined name such as =OFFSET(A1,0,0,COUNTA(A1:A100))
and you want to go the other way
=OFFSET(A1,0,0,,COUNTA(A1:M1))

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Noreaster" wrote:

I'm trying to transpose a chart example on Jon Peltier's website. I have too
much information to use it like the example. Row 2 would be Month, Row 3
would be Value A, Row 4 would be Value B. I believe I would have to make
changes in Define Name
variables and also in Chart Series. I'm not successful at the moment.

TIA