Solved: Automatically Update graph

[RECrerar]

Hi,

About three weeks ago I was looking on here for how to produce charts in excel that update automatically, without using VBA if possible (since some of my users have macros disabled on theor PCs).

So basically I want to do a summary graph that shows the total hours booked to different projects in the last 4 weeks and for this upadte automatically.

I found a fantastic thread on here which had examples of just how to do this, mainly using named ranges and so forth, butI can't find it again. Can anyone either point me in the direction of this thread or alternatively give me pointers on how to select which values to graph by data.

Thanks,
Robyn

[Bob Phillips]

Tell us what the data looks like, what need s to be selected to chart, what type of chart etc., and we can help you.

[RECrerar]

Hey,

have had a think about this and I think my question is wrong, since what I have currently is a spreadsheet, part of which looks like that attached. What I was originally thinking was trying to get the data into a plotable form directly from the raw data in the sheet, but what I think would actually be much much easier is to do a bit of data manipulation first.

Let me back up and explain. In the attachement, that columns of interest are A (data), C (subsystem) and E (hours worked) - the rest can be ignored for this purpose.

What I need to do is sum the hours worked by subsystem and date, so that the totals in Table 2 to be graphed show the total number of hours worked in the last month. I think I can figure out how to do this myself, but since I started a thread, thought I better reply and is someone wants to do it for me that would obviously save time.

So in suppary what I need is a sumif with 2 criteria (one that is by subsystem) and the second that is by previous month, that is if we are in May it will sum June's totals.

Thanks and sorry for the garbled question.

[Bob Phillips]

I just knocked up a pivotchart, took me 10 seconds.

[RECrerar]

Interesting, that was indeed very quick

I have to admit I'm not very good with pivot tables yet, but they do seem to be very useful things so I will use this as an example to actually get round to learning how to use them.

I assume you could get them to just show one month at a time as there are in actuality 14 subsystems and the project will run for a couple of years so it would end up looking pretty busy with all that information in the chart.

[Bob Phillips]

Becuase it is a pivot you can select the month that you look at. You can select May only,, April and May, all months, the choice is yours. This is ideal flexibility.

One thing I didn't mention is that when you first pivot it it will show all dates, so you will have to group by month.

[Bob Phillips]

Same pivotchart, I just selected May only.

[RECrerar]

okay, well I will look into it. Thanks for the pointers. Should actually get on with what I'm meant to be doing for a bit. But will try putting it into a pivot table as that does seem like a very neat solution.

Is there an option in the pivot table to group by month or do I need to do that with the raw data beforehand?

[Bob Phillips]

No it is a PT option. Select the date header, right-click, Group and Show Detail, Group... and select Months.

[RECrerar]

Excellent, that sounds simple enough, will try and get it working this evening

[RECrerar]

Hey just gave it a go and it's working great, it really is very simple, I've been thinking I should use pivot tables more and I think I shall try and do that in future, where appropriate.

Thanks