+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    44

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

    Hi guys.
    I need you help on this small mess of tables.
    You can see it here:

    http://i.imgur.com/dxqgxRj.jpg
    and file is attached. Book1.xlsx

    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?

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,641

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

    Try in E9:
    Please Login or Register  to view this content.
    E12:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-13-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    44

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

    Quote Originally Posted by bebo021999 View Post
    Try in E9:
    Please Login or Register  to view this content.
    E12:
    Please Login or Register  to view this content.
    works like a charm!

    Thank you!

    p.s. are my formulas in P and Q column correct/right ones to be used?!
    Last edited by yessuz; 04-18-2013 at 11:56 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1