Dynamic Charting

**k_excel** · 04-21-2008, 10:54 AM

everything i can find on charting the last 12 months dynamically is based on horizontally oriented data. my data is all vertically oriented so i thought that maybe i could just switch around the offset formula given as an example and that it would work, right? wrong. it didn't, maybe i shouldn't even be using that offset formula, who knows? can someone help? i found a really great example to use http://peltiertech.com/Excel/Charts/DynamicLast12.html. but like i said, it's for horizontally oriented data....please help

**oldchippy** · 04-23-2008, 03:51 AM

Hi,

Can't you just right-click on the chart, select Chart type and click Bar?

**robert111** · 04-23-2008, 07:36 AM

old chippy - i think the issue is that the data is in rows and the formulae he has for dynamic charts are based on columns...he may want a line chart

**k_excel** · 04-23-2008, 08:53 AM

I apologize for any confusion, but the direction the data is going is the problem. I am already using a bar chart, if you look at the link from my original post, it shows setting up your actual data in a clean little horizontal table so the offset formula is reading an entire column of data, from top to bottom. In my spreadsheet, the data is going straight across and I can't get the formula to work to even create the chart since my data is vertically oriented. It has something to do with the formula, either I need to modify it, but don't know how, or I need to use a completely different one.

**ChemistB** · 04-23-2008, 02:11 PM

See my post here.
http://www.excelforum.com/showthread.php?t=642649

Is that what you are looking for?

ChemistB

**gshock** · 04-23-2008, 04:59 PM

Originally Posted by ChemistB

See my post here.
http://www.excelforum.com/showthread.php?t=642649

Is that what you are looking for?

ChemistB

ChemistB beat me to it, but that should do the trick for you. I incorporated ChemistB's suggestions and they work great!

Goodluck,

-gshock

**k_excel** · 04-25-2008, 11:19 AM

hi, i think this may be it, but i'm not quite sure since i'm new to this way of charting. it's so confusing trying to apply it to an existing spreadsheet

. below is the spreadsheet in question, i only need to chart Value F that's all, but i would need a separate chart for each section. so i would need, one for Cars, one for Trucks, etc, etc. so will this work for this situation? also, i only need the last 13 months, so i can see the trend, ex: march 07 - march 08, but setting up the length was the easy part.

**gshock** · 04-25-2008, 11:23 AM

Originally Posted by k_excel

hi, i think this may be it, but i'm not quite sure since i'm new to this way of charting. it's so confusing trying to apply it to an existing spreadsheet

. below is the spreadsheet in question, i only need to chart Value F that's all, but i would need a separate chart for each section. so i would need, one for Cars, one for Trucks, etc, etc. so will this work for this situation?

k_excel, yes, this will work perfectly for what you're doing. I'm doing the exact same thing. I only want to chart one thing at a time, I just created separate charts for each one. I still create all the Dynamic range names, but each chart only graphs one range name.

Let me know if this is unclear and I can show you the chart I created. You can pick it apart if you need to.

-gshock

**k_excel** · 04-25-2008, 11:26 AM

ok great, let me go and try and i'll let you know. thank you for your quick response gshock

**gshock** · 04-25-2008, 11:31 AM

Originally Posted by k_excel

ok great, let me go and try and i'll let you know. thank you for your quick response gshock

k_excel, anytime. Glad I can help. I'm happy to finally have the chance to give something back after all the help I've received.

**k_excel** · 04-25-2008, 01:28 PM

ok, according to my spreadsheet i attached in the last post, this is how the formula should be, yet it's not working

i don't know what to do at this point, i know i'm missing something, but what??

=OFFSET(sheet1!$C$8,,MAX(0,COUNTA(sheet1!$8:$8)-sheet1!$A$2),,MIN(sheet1!$A$2,COUNTA(sheet1!$8:$8)))

**ChemistB** · 04-25-2008, 01:59 PM

Your formula

Please Login or Register  to view this content.

Correct formula

Please Login or Register  to view this content.

You were very close. Let's work backwards. The last argument in your function is COUNTA(sheet1$8:$8). This argument is telling Excel how wide you want your final data range to be. You really only want it to be equal to the value you put in A2 so should be Sheet1!$A$2.

Your second argument

Please Login or Register  to view this content.

is very close. You just need to subtract any cells in row 8 that don't have values in them (including hidden column B). so just subtract 2 from it.

On a side note, your spreadsheet has a lot of errors #DIV/0 which are going to screw up this formula unless you fix them.

Try these changes and see if it works for you. When you are in "Define Name", if you click inside the box that contains your formula, it shows you the exact range with dotted lines that it is looking at.

Good luck

ChemistB