Posted: 10/6/2006 5:34:00 AM EDT
|
For the life of me, I can't figure out how to do this, and I'm wasting more time updating charts than entering the damned data. If I have a chart by month (JAN, FEB, MAR, etc.), how do I get a trend chart for that data (with Months as the x-axis) to update automatically each month when I enter the new data? Sure, I could have the chart have JAN through DEC, and have all the months that have no data show up as blank, but that sucks. What I'd REALLY like is to display the current month and the previous 11 months, with the chart "sliding over" one month automatically when I enter the next month's data. It's a PITA to go into 30+ charts and have to change the column reference for each element by hand each month. Thanks. |
|
You can't, Excel bases the chart on your selected area, and there's no way I know of to automatically expand that area. I'm not sure what you mean my manually "changing the column reference", but changing the "series data" will do the trick and isn't too hard. An elaborate work around is to create a static table that scans your main table and collects data for the past 11 months. The logic would use if statements to say if the Dec column is not blank, use Dec, then work your way backward. The problem is Excel can only do something like 7 nested "if" statements so you can only go back 7 months. Plus it would be more trouble than just redoing the charts by hand. |
that's how i always dealt with auto-updates. had my monthly sheets linked to a running yearly sheet that was already set up. |
|
What about this option: Develop a macro to highlight your complete data set (either the whole page, or whole rows), copy it and paste it to a new sheet, then data sort it to get the highest dates first. Then base your charts on the second sheet. Every time you run the macro, you will update everything all at once. Not positive it will work, but it might be worth a shot. ETA, I tried it and it works. Run one macro after you enter your data and all charts get updated all at once. If you need more details IM me or email me. |
| This is one of those things that was SO MUCH ESIER in eXcel 4 (circa 1995) than in any of the post 1995 eXcels. In the older eXcels, one had access to edit series and could simply edit the termination point of each line in the graph. I keep an old eXcel on my computer for exactly this reason. |
|
I keep track of Formula One points and such on my own spreadsheet. Yeah, I'm a geek like that. For my chart, I have a separate worksheet that compiles the data that I want to display. If you did something like that, you could have fixed ranges for your graph series, but vase variable data in the source ranges, based on whatever criteria you select. The calculation for that is not difficult at all, and then you'll just need to do a VLOOKUP or HLOOKUP (depending on how your data is arranged) to fill out the rows/columns in your chart sheet. |
Unless I'm not following you, you can still do that, but it's a PITA. Since I don't know VBA, I did it this way: 1) Set up master data table. This is where the data will be entered each month. 2) Set up a HLOOKUP table. The headers of that table are keyed off a date you select, and calculate backwards using EDATE for each month, up to 12 months total. 3) have a single field where you enter the date you want your trend charts to end as of. 4) Link charts to HLOOKUP table. End of problem. Been working on it all day, and only now am finishing the tables.  Still pretty cool to have figured out though! Just hope the boss & co. appreciate it.  ETA: DzlBnz and I must think alike. RUN AWAY! 
|