Fixed data source location for a graph.

Hatterfan

New Member
Joined
May 29, 2015
Messages
5
Hi all.

I have searched high and low on the internet, using every ‘key term’ I can
think of to no avail so I am putting this here in the hope someone has a
brainwave. If you know a thread of this sort already exists – please send me
there.

I want to be able to create a graph in which the location of the source data
does not change regardless of any rows or columns that may be inserted.

I have a data set which looks at the income generated by
three teams each week. The total income is listed on the Monday of each week.

Week Commencing
Team 1
Team 2
Team 3
23/02/2015
£500.00
£460.00
£660.00
16/02/2015
£700.00
£340.00
£675.00
09/02/2015
£750.00
£250.00
£650.00
02/02/2015
£600.00
£375.00
£700.00
26/01/2015
£500.00
£350.00
£740.00
19/01/2015
£400.00
£450.00
£775.00
12/01/2015
£750.00
£300.00
£850.00
05/01/2015
£500.00
£250.00
£800.00

<tbody>
</tbody>


I then use this data to create a line graph. The graph clearly uses the range A1:D9 for its data source which I want it to continually use.

I want to then insert a new row 2 for Week Commencing 2
March 2015. I want the graph to update with this new information but to drop
the 5 January 2015. Ie – maintaining the data source A1:D9.

Unfortunately, if I simply add the new row – the data source breaks
(maintaining the heading but now taking the figures from B3:D10.

If I add a row in the middle of the data set and then move it back into place
in order to add the data I want, the data sources expands incorporating my new
data but keeping the January 5th
entry. A1:D10.

From my research, the use of ‘dynamic’ charts is simply a more efficient way of
doing the latter – allowing a graph to grow automatically.

All I want is for the graph location to be fixed regardless of any other
changes but to update the corresponding data.

Hope that provides enough information to explain what I am after but shout if
you have any questions – any help is appreciated (or even workarounds).

Thanks,

James.



 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
I dumped your data into excel at A1, which runs out to D9

I selected from H26 to A1 to give more lines and columns

in the reference bar where you see the row / column numbers I typed GraphData (which creates a named range)

I then went to the graphs tool and added a blank graph, right click on that and then select data source "type GraphData"

the gives you a source that will accept more than you currently use referenced by name only

hiding rows or columns drops out bits you don't need to see
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top