Help Transpose, Chart Last 12 Month Dynamically

[Noreaster]

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

 

[ShaneDevenshire]

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.

 

[Noreaster]

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]

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.

 

[Noreaster]

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.