INDIRECT or VlookUp Formula Help required for a Validation List

  • Hi,
    I have a template where I have 9 Towers and in that i have different Queues and i want to pull the data from the 6 Work Sheets to the template (Working File).
    In the Working File WS (E2) Cell I have a Validation list (P1, P2, P3, P4, P5, PNone) and Validation List (01-May-14, 02-May-14)


    My Requirement is When i select P1 from the drop down (E11), the data should be pulled from the Pivot WS (A5) and when i select the date from the drop down (D11) the data should be pulled from the Pivot WS (Open Date) for the each group in the tower.
    Please Note that based on the Date selected from the Drop down the P1, P2, P3.....PNone numbers should reflect, When i change the date the P1, P2, P3 drop down list should display the count for that day (Open Date in Pivot WS)
    Other Requirement is, Achievement % (Formula is already provided by NBVC, just want to update it -- Please find below)
    [
    Requirement is Achievement % is L13/50% of S5 [L13 Divided by 50% of S5 (For P1, Tower 1, L13 to L22, S5 ........... till Tower 9)
    (For P2, Tower 1, L13 to L22, T5 | ........... till Tower 9)
    '
    '
    '
    Same for all the priority.
    ]


    [This data will be updated daily]


    MTD --> MTD is the % Achievement from Overall Total P1 Incidents/50% of the S5 for each tower.
    Same for P2, P3, P4 and the towers subsequently
    Please help me in finding out the formula for this kind of requirement. Or I have also included the sample data, please suggest a formula which will best suit my requirement as per the Excel Report template.


    Sample file attached.

  • Re: INDIRECT or VlookUp Formula Help required for a Validation List


    Are you sure you have the right file? I don't see date drop down nor do I see dates in the other sheets.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: INDIRECT or VlookUp Formula Help required for a Validation List


    Instead of using the Pivot table, it would be better to use the main data, since all rows have info instead of the merging that pivot tables do....


    Try formula in E13:


    [COLOR="#0000FF"]=COUNTIFS('Technology Incidents'!$G:$G,SUBSTITUTE($E$11,"P",""),'Technology Incidents'!$B:$B,$D$11,'Technology Incidents'!$H:$H,E$12,'Technology Incidents'!$F:$F,$C13)[/COLOR]


    copied down and across, then copied to E24, and copied down and across, and so on.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: INDIRECT or VlookUp Formula Help required for a Validation List


    Hi NVBC,


    The formula is not working. I can replace the values in the Priority Column in the data (Replace 1 with P1, 2 with P2.... Pnone) if you want. Please help me in getting another alternative and also Calculation for Achievement % and the MTD % please..

  • Re: INDIRECT or VlookUp Formula Help required for a Validation List


    Try changing date in D11 to May 6th and the Priority in E11 to P5. You should see values populate.


    If not try entering the formula again.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: INDIRECT or VlookUp Formula Help required for a Validation List


    Hi NBVC,


    Thanks, it worked like a charm. Also want to know the Formula which i can use for the MTD %


    MTD --> MTD is the % Achievement from
    Overall Total P1 Incidents in the data (irrespective of data)/T14 for Tower 1 and Queue 1 (N13/T14) same for all the Queues in Tower 1
    Same for P2, P3, P4 and the towers subsequently


    Overall Total P1 Incidents in the data (irrespective of data)/T15 for Tower 2 and Queue 1 (N24/T15) same for all the Queues in Tower 1
    Same for P2, P3, P4 and the towers subsequently


    Apart from this, as the data is huge and i will be adding the data on a daily basis it will be increasing and there is lot of processing time due to the formulas. Is there any way that this can be incorporated in the Macro for quick and reducing the Processing time.

  • Re: INDIRECT or VlookUp Formula Help required for a Validation List


    Hi NBVC,


    As of now MTD % formula is fine for me. No need for a macro to reduce the processing time, if there is a way to reduce please help me in that.


    Thanks in advance

  • Re: INDIRECT or VlookUp Formula Help required for a Validation List


    Hi NBVC,


    Sorry for one more reply, the formula is little bit urgent...... Please help