Source Data for Graphs

hey yall,
i have recently inherited some Excel files that contain graphs. i edit the information and update the graphs each month. Usually when i click on a graph boxes will appear that show you the data that is being used. When you want to update the graph you just increase the range by moving the colored lines. However, some of the graphs that my boss initially created do not work that way. i have to go into the source data and recapture the data each time.

This is not that big a deal, but i was wondering how to fix them so that they work in the same manner as the ones i described first...

thanks

Hi!

It really depends on what kind of graphs you have...

most are simple bar graphs, a couple are line graphs. none of them are real complex...

Attached is an example book. There are 2 dynamic named ranges using the INDEX/MATCH functions. There are a lot of examples out there using the OFFSET function, but I'm just not a big fan, or convinced it's any better.

So check out the two defined names (Insert -> Names -> Define) MyXdata & MyYdata.

After you define your names like the formulas, the only other thing you need to do, is click on your chart, then click the formula bar, type in a formula similar to this:

=SERIES(,MyXdata,MyYdata,1)

Confirm with enter. Chart will update when sheet is calculated.

Note: Named range formulas are looking down column C and B starting from row 8. It's looking for numerical values. Does this help any?

:confused: unfortunately you completely lost me. i have never seen the name function before... i was not even able to follow the concept...


here is an example of what i am used to. if you click anywhere on the graph the lines show up around the data and the labels and all... and if i move the line around the data, excel moves the label lines along with it. in the other reports, the only time i see those boxes is when i click directly on the data points within the graph, and when i change one it does not move the label one with it...

as i said, this is not a big deal, i am just trying to understand what is different about them that causes them to react differently...

thanks for your time - sorry i am so dense sometimes...

Oh, stop being hard on yourself silly. :)

On your graph, are you wanting to keep the x-axis as Jan - Jun and wanting to have the data grow with all that is to the right of it?

well, when i enter the data at the end of July i will move those boxes down so that it still onlys shows a 6 month range, but on another report i may just grab the corner of the box and drag it so that it keeps all the old data and captures the next cell as well...

Ok, you could start from a specific range and go down dynamically, or you could do the last six months dynamically.

But will you be entering dat in more columns, or rows? The rows I see will grow, the columns is the question. You just need to specify the starting and ending points, and if they are dynamic - and their rules if so.

as of right now there will not be any more columns... we plan to expand eventually, but i am sure that is another year or so away. obviously the rows will continue to grow each month.

i just cant figure out why when i click anywhere on that chart it outlines the data sources and allows me to manipulate them that way, but on other graphs the only way to see the outlines is to click directly on the data points, and then i still have to right click and choose SOURCE DATA so i can change the range that appears...

Quote:

---

Originally Posted by cmpgeek

i just cant figure out why when i click anywhere on that chart it outlines the data sources and allows me to manipulate them that way, but on other graphs the only way to see the outlines is to click directly on the data points, and then i still have to right click and choose SOURCE DATA so i can change the range that appears...

---

I am thinking that it might have to do with split data sources. For instance, on your example, the data is all contained within the one block (even though it is separate from the column headings). Can you check and see whether the other graphs that do not allow this are split?

Or sometimes the data is set up using the Series tab rather than the Data Range tab. I think that those that are set up with Series tab do not respond the same as Data Range graphs.

Shades,
you hit the nail on the head! One of the charts that was giving me trouble did indeed have the data split. as soon as i placed that row next to the other row and click in the white area around the chart it outlined the data sources the wat my others do...

thank you so much!

So were you still wanting some work done on this graph? To make it dynamic? If so, how? Or did Shades get it done for ya? :)

have you ever watched the movie Philadelphia? there is a phrase in that movie that fits me well sometimes...

can you please explain the dynamic concept to me - "like i am a three yr old"...:blush

one of the downfalls to teaching yourself most of what you know, is that you dont always understand the terminology used...

i have one chart set up right now that updates as soon as i enter a number into a designated field... i love it except that all the fields to the right of the one i am working in have 0s in there so it always appears to be bottoming out lol...

Then that's dynamic. :) Dynamic means that you don't have to keep going into the Source Data.. and changing where the chart *sees* it's information from, that it will update automatically. This is usually done with some sort of a)SERIES formula, b)an OFFSET function using named ranges, or c)INDEX/MATCH combo using named ranges (my personal favorite), or a combination. But it sounds like you already have it dynamic. :yes

Just wondering if there was anything else you needed help w/ on this. :dunno

Quote:

---

Originally Posted by cmpgeek

Shades,
you hit the nail on the head! One of the charts that was giving me trouble did indeed have the data split. as soon as i placed that row next to the other row and click in the white area around the chart it outlined the data sources the wat my others do...

thank you so much!

---

You're welcome. Glad to help.

Hey, this might be a first for me - giving advice that works!! ;)

Quote:

---

Originally Posted by firefytr

This is usually done with some sort of a)SERIES formula, b)an OFFSET function using named ranges, or c)INDEX/MATCH combo using named ranges (my personal favorite), or a combination. Just wondering if there was anything else you needed help w/ on this. :dunno

---

honestly i cant tell you which way fits, i have all our surgeons grouped together by their service... i enter the number of cases each surgeon does on a monthly basis and it gives me a total # per service.

on a seperate sheet i have the grand totals showing up via a VLOOKUP field that refers to the first sheet. the chart is also located on this sheet and the source data for the chart is the collection of fields on the second sheet. as i enter each individual surgeon's total on the detail sheet, the total grows with it... as does the coinciding <sp?> cell on the second sheet. as the cell on the second sheet changes it changes the chart... the only draw back i have found is that - since i have all the VLOOKUP formulas already in place for the rest of this year, i have 0s for future months so you watch the data "travel" each previous month, and then plummet to zero for the months that are not completed yet... a bit irritating, yes, but it still does what it is supposed to and that is what is important.

i guess in theory i could remove the VLOOKUP fields for future months and then copy the ones from the previous month and see if that works (i cant think of a reason why it would not, but i have not worked much with VLOOKUP fields as of yet either)... i may have to play with that a bit...

thanks to both you and shades for all your help with this... i am humbled by yall's "gray matter" hehe :ipray:

If I understood you correctly (w/ a grain of salt, lol) you may be able to use...

=IF(VLOOKUP(x,xx,x,x)=0,"",VLOOKUP(x,xx,x,x))

Or if you download install the morefunc.xll addin, a great tool, you can make use of other functions and use this - much more efficient...

=IF(SETV(VLOOKUP(x,xx,x,x))=0,"",GETV())

Quote:

---

Originally Posted by cmpgeek

i am humbled by yall's "gray matter" hehe :ipray:

---

That ain't all that's gray!!

On the serious side, Walkenbach provides detail on how to set up rolling periods for graphs (i.e. always a six month period), in his book Excel Charts. BTW a superb resource if you work with charts much.

Or check out this from Andy Pope:

http://andypope.info/charts/Scrolling.htm

as a starting point.

thanks to both of you for the additinal info! i will take a look at these asap!

thanks again!