Create new worksheet with one graph using single cell input from multiple worksheets

Steffi_Kaizun 

There is a strange dissonance about this discussion.  You are an (ex?)-programmer who appears not to want programmatic solutions and is using software that is beyond obsolete!

In the 1970s the way of achieving your goal would be as a manual bookkeeping exercise.  In January each year you would add the total for the prior year to the summary sheet, just as you appear to have done now!

In the 1980s Dan Bricklin had the brilliant idea of a system that would allow users to access data by 'point and click' without programming knowledge.  That would allow you to type '=', switch tabs, and click the total cell.  That was regarded as just about as good as life could be, the user accesses their numbers without the "tedium" of defining variable names.

So what about now?  The Excel formula now offers a Turing complete programming language but working across sheets is still not a great experience.  Power Query (2010) would do the job of appending data from multiple Tables (2007) but may represent a measure of overkill, especially so if you don't have access to the code.

Personally, with 365, I would use a 3D helper range with the year and total

=TEXTAFTER(CELL("filename",A1),"]")

= Data2001[[#Totals],[Value]]

Naming the 3D range 'annualValues' the data for plotting would be returned by

= VSTACK(annualValues)

A lot more effort by the way of set-up but with payoff later; that is the nature of programming!