Excel Help Needed

s1lkster

s1lkster

s1lkster

Associate
Joined
3 May 2006
Posts
6
good morning chaps,

i have set u a spreadsheet which produces a number of graphs, every month i add new figures to the spreadsheet and the graphs then need updating.

the graphs have a column on them for each month.

at the moment i have to go into the 'source data' for each graph in turn and extend the area to include the next months figures.

is there a way the graphs can be automatically updated when new data is entered? or a quicker/easier way of doing this than my current method?

thanks for your help

paul :)
 
Yes it can be done! :)

But its "relatively" complex, at least to try an explain....

But I'll do my best.....

You need to use "defined" fields for your "source data", and you can have an "intelligent" defined field.

Lets start with the basics.....(Defined fields)

Go to the values in your spreadsheet that make up your x-axis - select them all and go to:

Insert - Name - Define

And pick a "name" for your range (eg "xaxis")

Now you can do the same for your y-axis.

Now go to your graph and change the "source data" so that it doesn't point to the CELL RANGE - it points to that DEFINED NAME!

eg: Category X Axis Labels: =Worksheet!xaxis

where "worksheet" is the name of the worksheet in which the range is DEFINED (probably the same worksheet as you're working in, unless you've got the cell data on one sheet and the graph on another!)

Get this to work first for a STATIC defined range - ie, its no different to what you have now!! Then we can create an "intelligent range" (i hope ;) - i have got this working myself - so i should be able to expain!!)
 
thank you for the reply, sorry ive not been in the office again since i left hte post, but i have done this today and managed to get up to where you left it done without to many problems, so i think i am now upto trying the next section!!

Thanks Paul :)
 
Ok I'll do my very best to explain this - just give me a shout if there's anything you don't understand.....

If you go back to Insert - Name - Define....

you will (probably) have "something" like:

xaxis: =$A$1:$A$8

The "COUNTA" function can be used to count how many cells in a column have "data" in them....ie how many are not left blank....

Eg (for example) if in cell C1 you type: "=COUNTA(B:B)"

You will get a number in C1 corresponding to the number of cells in Column B that have data in them....

You can also use the "OFFSET" function in your Name - Define field to specify a range.

OFFSET takes five fields, eg OFFSET(v, w, x, y, z)

v=Reference cell
w=Rows above/below Reference (+ve = Below, -ve = Above)
x=Columns left/right of reference (+ve = Right, -ve = left)
y=Height (for a dealing with a Range)
z=Width (for a dealing with a Range)

Your graph will be made up of however many "months" are typed in a particular column I presume?!?!

So you can use COUNTA to establish how many months are in your range....and OFFSET to define the range....

THE END RESULT:

And so your Insert - Name - Define becomes something like:

xaxis: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

(before it was something like, xaxis: =$A$1:$A$8)

Now as you add months to column A you will increase your range, which will automatically be changed on your graph - because of the fact that your graph references the "defined range", as described the other day!!

Obviously your offset command will vary depending on which column your data is in....

And if your "months" column has a "header/title" in A1, then your "reference cell will be A2. IE the first cell that you have a MONTH typed into.....
 
I'm not sure how well I explainedit, but I'm 99% certain that it'll do what you are trying to do.

There's a good chance I may not have explained it very clearly though!! :)

Please let me know how you get on - hopefully you'll have no problems.

If needs be - you could send me a sample sheet - it DOESNT have to contain "actual" data if your data is in any way private or sensitive.....just something that gives me an idea of your spreadsheet and layout etc.

Then I could have a go at explaining things a bit more SPECIFICALLY!! :)

Hopefully you won't need to do that - but it is quite hard to explain all this in a very general manner.

Good luck :)
 
hi,

thanks for this, i will have a play with the information you have given me this afternoon, i get the general concept so may be able to do it or get somewhere close!!

if not i will let you know!! but thanks again for your time and effort in explaining it!!


Paul
 
You must log in or register to reply here.
Back
Top Bottom