Forum Discussion

mohsin91santono-_-1220's avatar
mohsin91santono-_-1220
Brass Contributor
Mar 18, 2019
Solved

To Lookup For Top Name of Column And Row In A Chart For A Specific Value And Create Another Chart

Hello There,

 

I need big help.

Attached images is self explanatory.

 

Let me know if you need any other information

 

Let's say, I need to create alert system for a specific date

 

Thanks.

excel
  • Celia_Alves's avatar
    Celia_Alves
    Mar 19, 2019

    mohsin91santono-_-1220 

    Here you go! :-) I developed a solution only based in formulas and not requiring Power Query.

     

    Data Table:

    I transformed the data table into an Excel Table. That will allow you to add more data at the bottom without having to update the ranges in the formulas. The formulas will always know that they need to look at the entire data table, regardless of the number of rows.

    I added three extra columns to the table that will be responsible to check if the columns B, C and D have dates that coincide with the date in cell I2. You can hide these three helper columns if you'd like.

    Cell I2 was given the name ChosenDate.

    I added conditional formatting rules to the data table. This way, any date that matches the ChosenDate will be automatically highlighted. If you want this feature to keep working, you cannot paint the cells of the data table.

     

    Results Table:

    I added two helper columns to this table that are responsible to pick all the matching entries in the data table. You can hide these two columns if you'd like.

    I added the formulas to the report columns.

    The report table can show up to 25 results. You can copy the formulas down if you need more result rows.

     

    The final file is attached.

     

    I hope this helps.

    Celia Alves 

     

10 Replies

Resources