Hi guys.
I need you help on this small mess of tables.
You can see it here:
http://i.imgur.com/dxqgxRj.jpg[/URL]
and I can send the file to email (cannot attach here??)
Short desription:
These tables are in „Sheet 2“.
Table on lines 1-3 shows:
1: Current Year to date target. It is set once a year and might be updated let‘s say once a year. Cell P1 checks for the last data and displays that: [forumula in this cell is =INDEX(D1:O1,MATCH(9.99999999999999E+307,D1:O1)) ]
2: Current, Actual figures. Those are updated each month. And data in P2 looks for the latest figures and displays that. The formula is: [=IF(O2<>0,O2,IF(N2<>0,N2,IF(M2<>0,M2,IF(L2<>0,L2,IF(K2<>0,K2,IF(J2<>0,J2,IF(I2<>0,I2,IF(H2<>0,H2,IF(G2<>0,G2,IF(F2<>0,F2,IF(E2<>0,E2,IF(D2<>0,D2,0)))))))))))) ]
3: Forecast – is the forecast of course and it is done in the begining of year, later it can be updated any time. P3 has this formula: [ =INDEX(D3:O3,MATCH(9.99999999999999E+307,D3:O3)) ]
Column H is for AUGUST. (financial year starts from April, finishes in March)
Data from this table is used to draw a simple line chart.
Tha tables B9:K14 are made as the control table, which helps to display the bullet chart. It works like this:
Table named „chart data“ (G9:K14) takes data from D9:E14, divides and plots the chart as I need to. The bullet chart shows the last month target (Blue bar), current situation (red line), this month target (Black dash) and year target (Grey). Light grey is „over spill“.
The PROBLEM:
Table „CONTROLS“ now takes the static data from the table (I added reference to the cells). It means: it takes the data from cells for AUGUST (last month and this month data). But it is static. I mean: if the data is added for September, that thable (Controls) should take the data from line 1 from cells I and J. (hope you understand what I mean..)
There is a drop down menu in SHEET1 cell B2 which choses months (April – March) , and as result, displays the result in line 5. (if you choose the July, it will show that result is „Target Exceeded“).
What I need: to display the Bullet Chart for the CHOSEN Month. I mean: if I choose the July, the Controls Table takes data from the appropriate months, and draws a bullet chart.
The question:
What would be the best formulas to use in E9:14 cells, which would allow me to choose the data for the right month in accordance which month was chosen in Sheet 1 B2?
I need you help on this small mess of tables.
You can see it here:
http://i.imgur.com/dxqgxRj.jpg[/URL]
and I can send the file to email (cannot attach here??)
Short desription:
These tables are in „Sheet 2“.
Table on lines 1-3 shows:
1: Current Year to date target. It is set once a year and might be updated let‘s say once a year. Cell P1 checks for the last data and displays that: [forumula in this cell is =INDEX(D1:O1,MATCH(9.99999999999999E+307,D1:O1)) ]
2: Current, Actual figures. Those are updated each month. And data in P2 looks for the latest figures and displays that. The formula is: [=IF(O2<>0,O2,IF(N2<>0,N2,IF(M2<>0,M2,IF(L2<>0,L2,IF(K2<>0,K2,IF(J2<>0,J2,IF(I2<>0,I2,IF(H2<>0,H2,IF(G2<>0,G2,IF(F2<>0,F2,IF(E2<>0,E2,IF(D2<>0,D2,0)))))))))))) ]
3: Forecast – is the forecast of course and it is done in the begining of year, later it can be updated any time. P3 has this formula: [ =INDEX(D3:O3,MATCH(9.99999999999999E+307,D3:O3)) ]
Column H is for AUGUST. (financial year starts from April, finishes in March)
Data from this table is used to draw a simple line chart.
Tha tables B9:K14 are made as the control table, which helps to display the bullet chart. It works like this:
Table named „chart data“ (G9:K14) takes data from D9:E14, divides and plots the chart as I need to. The bullet chart shows the last month target (Blue bar), current situation (red line), this month target (Black dash) and year target (Grey). Light grey is „over spill“.
The PROBLEM:
Table „CONTROLS“ now takes the static data from the table (I added reference to the cells). It means: it takes the data from cells for AUGUST (last month and this month data). But it is static. I mean: if the data is added for September, that thable (Controls) should take the data from line 1 from cells I and J. (hope you understand what I mean..)
There is a drop down menu in SHEET1 cell B2 which choses months (April – March) , and as result, displays the result in line 5. (if you choose the July, it will show that result is „Target Exceeded“).
What I need: to display the Bullet Chart for the CHOSEN Month. I mean: if I choose the July, the Controls Table takes data from the appropriate months, and draws a bullet chart.
The question:
What would be the best formulas to use in E9:14 cells, which would allow me to choose the data for the right month in accordance which month was chosen in Sheet 1 B2?
