# Need help with appropriate formula which would choose right data from right month

#### yessuz

##### New Member
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?

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Replies
1
Views
330
Replies
0
Views
215
Replies
3
Views
785
Replies
2
Views
564
Replies
0
Views
302

1,196,073
Messages
6,013,270
Members
441,759
Latest member
ab_

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back