Forum Discussion

Dinesh Kumar's avatar
Dinesh Kumar
Copper Contributor
Aug 23, 2018
Solved

To build a dashboard in excel

I would like to make a dashboard highlighted in G,H,I Columns with data available from B,C,D columns. When I enter the name of the person in cell H2, i should automatically get the data in G5 ...
excel
  • Philip West's avatar
    Philip West
    Aug 23, 2018

    There are a couple of formulas that will let you do this.. but here is one that I learnt from someone (Sergei Baklan) on this board a few day back.

     

    It avoids the use of an array formula which your CPU will thanks you for.

     

    I've attached an example using something similar to what you posted showing it working. Hopefully it helps.

     

    The formula used is this:

    =IFERROR(INDEX(tbl_data[Person Name],AGGREGATE(15,6,(1/(tbl_data[Person Name]=$H$2)/NOT(ISBLANK(tbl_data[Person Name]))*ROW(tbl_data[Person Name])-ROW(tbl_data[[#Headers],[Person Name]])),ROW(A1))),"")

Philip West's avatar
Philip West
Iron Contributor
Aug 23, 2018

There are a couple of formulas that will let you do this.. but here is one that I learnt from someone (Sergei Baklan) on this board a few day back.

 

It avoids the use of an array formula which your CPU will thanks you for.

 

I've attached an example using something similar to what you posted showing it working. Hopefully it helps.

 

The formula used is this:

=IFERROR(INDEX(tbl_data[Person Name],AGGREGATE(15,6,(1/(tbl_data[Person Name]=$H$2)/NOT(ISBLANK(tbl_data[Person Name]))*ROW(tbl_data[Person Name])-ROW(tbl_data[[#Headers],[Person Name]])),ROW(A1))),"")

  • Dinesh Kumar's avatar
    Dinesh Kumar
    Copper Contributor
    Aug 24, 2018

    Thank you so much Philip.. have a wonderful day ahead!

Resources