I have hooked up excel to an auto-updating database, but now I want to go a step further and make all essential report items automatic as well. And not only that - I want to make the way report looks easily controllable by few values.
So to the core of problem.
Say, I have data in the form of:
2012 I 3.6
II 3.4
III 3.3
IV 2.8
V 2.2
VI 1.9
VII 1.7
VIII 1.7
IX 1.9
X 1.6
XI 1.5
XII 1.6
2013 I 0.6
II 0.3
III 0.2
IV -0.3
V -0.1
VI 0.2
VII 0.3
VIII -0.2
IX -0.4
X -0.1
XI -0.4
XII -0.4
From here I can make an object in name manager with (example):
=OFFSET(CPI!$C$2;0;0;COUNT(CPI!$C$2:$C$145))
This will count all cells that have some value in them and auto update the chart that refers to the name object as values get added. But what I want is the $C$2 reference in both OFFSET() and COUNT() to be controllable. By that I mean, as an example, I have a cell $X$1 with value 2012 in it which determines the starting point of name object and hence chart that refers to it.
I do know how to refer to a specific cell by matching cell values with VLOOKUP() but it returns the value of cell, not the coordinates. Hence it is of no use (or at least I think so).
INDIRECT
formula.=INDIRECT("$C"&2)
gives you the value in cell $C2, for example. You could make the "$C" and 2 dynamic in this example