2

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).

2
  • It sounds like you need the 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
    – CallumDA
    Oct 20 '14 at 11:02
  • I think I am missing some logic here. By having cell name as a string that can be controlled from some other cell, I still don't understand the way I can type in, say, 2005 in a certain cell, and get all charts start from that date/year. I have data with varying frequency, so lining up all charts perfectly so that certain date is at certain coordinate is not an option.
    – statespace
    Oct 20 '14 at 11:24
0

Another method is below, I used my own values to test:

Note: You will need to replace my commas with semi-colons for your system

Using the named range formula and the setup below will start your the named range from the year you selected and continue down until the bottom of your data (or C1000, if that comes first)

=OFFSET(Sheet1!$C$1,MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)-1,,COUNTA(INDIRECT("C"&MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)&":C1000")))

Note the C1000 at the end of the formula - you could make that C15000 or similar if you have more data.

enter image description here

The method is as follows:

OFFSET(
Start: C1
Rows: Find year using MATCH() formula and move down this many cells -1
Columns: N/A
Height: INDIRECT effectively creates the range between Cx, where x is the row of the 
        selected year and C1000. Then using COUNTA over this range gives the required 
        height of the range
Width: N/A
)
1
  • Yes. YES. Perfect. With slight modifications to your code it worked. Final version of my specific example is =OFFSET(INDIRECT("CPI!"&"C"&MATCH(Info!$C$3;CPI!$A:$A;0));0;0;COUNT(INDIRECT("CPI!"&"C"&MATCH(Info!$C$3;CPI!$A:$A;0)):CPI!$C$1000)). You probably want to slightly modify your code in answer. But yeah, thanks alot!
    – statespace
    Oct 21 '14 at 10:42
1

I'd suggest using a pivot chart

set up your data slightly differently (as below) and drag the following fields into a pivot chart.

Filter: Year
Row Values: RN
Values: Value

enter image description here

Filter on the year you need

1
  • To be honest, I feared of this being the only option. Given that my data tables have quite a lot of calculations within excel, I'm afraid pivot table is not quite an option as well. It would require to transform, say, 20ish data tables with 20sh columns each (with interlinked calculations) and 150sh observations into long format. Don't think this will work for my specific case. Although this is a valid solution for this problem in most of cases.
    – statespace
    Oct 20 '14 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.