I have a table in which I draw a graph based on data from a column in another sheet in an excel spreadsheet

This is a simplified version of the actual data

  A             

1 Col 1

2 10

3 11

4 12

The data I am doing a graph of is on Col A, labelled Col 1

In a second worksheet I have a table to graph data in Col A,and in the table you set what data to graph by getting to the edit series part, and I then enter this to get the data:

=(‘Sheet1’!$a$2:$a$4)

This means that each time I add a row to Col a, I have to edit the above

What I would like to do is change this so that when I add a row to Col a, the graph automatically picks up this change

I’ve done various searches on how to do this, and it seems I should try setting up a filter called data, and set that filter to use an INDIRECT string like this

=INDIRECT(“‘Sheet1’!A2:A”&D3)

where D3 is set to =LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A))

The above expression to set D3 works ok

And then in the edit series in the table use:

=’Sheet1′!data

But I have not been able to get this to work. I’ve tried various combinations etc.

Any advice/examples on how to do this would be appreciated