How to: Take data in a row and show it in a column in a dashboard.

I'm looking for some ideas how to solve this.

I have data in a sheet that is in one row. 4 of the columns are the same type of data like phone numbers and descriptions of the phone numbers use like main or fax. In the dashboard I need these numbers and descriptions in column format for readability since we could have up to 10 or more numbers to display. I don't have access to apps.

Here is sample data:

Here is the dashboard's desired state. (data manipulated to show the end result)

I've tried using formulas like index and such that works when the data is in row 1 but when it's not the formula returns nothing. I am using a report, I haven't found how to transform the data in a report to accomplish what I need.

Updating or changing the way data is being added to the sheet would not be a good flow for our customers.


Thank you,

Roy

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Roy C

    Glad we got it working!

    For others looking at this post.

    We used an INDEX/COLLECT structure.

    =
    IFERROR(INDEX(COLLECT([phone 4]:[phone 4], [phone 4]:[phone 4], <>""), 1), "")
    

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!