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
-
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
bonsoir Roy
je ne peux que te conseiller au niveau de mes capacités... je suis nouvelle en usage entreprise
que je ferais :
- créer un rapport tu sélectionnes ta feuille source
- tu choisis le nombre de colonnes et les colonnes dans l’ordre voulu
c’est pas mal mais je cherche à pouvoir supprimer des lignes : apparemment dans un rapport et pas possible de creer des lignes parents/enfants pas de personnalisation de cellule... quel dommage ici
bonne chance, Ness
bonne f i n d’annee
ne😅
-
Hi Ness,
Thank you for taking a look.
Roy
-
j ai trouve pour supprimer une ligne dans un rapport :
clic sur une cellule de la ligne et clic clavier sup et rafraichir la feuille
bonne nuit
-
Hi @Roy C
I hope you're well and safe!
Will it always be one row on one sheet, or how would it be structured?
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
I hope that helps!
Have a fantastic week & Happy Holidays!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andree,
Thank you very much for solving my issue!
I appreciate the effort and solving this problem for me.
Roy
-
You're more than welcome!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Bonjour,
Je voulais savoir quelle formule avez vous précisément utiliser. En effet j’ai essayé avec IFERROR(INDEX(COLLECT([phone 4]:[phone 4], [phone 4]:[phone 4], <> » »), 1), « ») cependant, j'ai pas réussi à l'appliquer pour plusieurs colonnes et donc plusieurs critères
-
Hi @lea.s6
Currently the Smartsheet Community only supports English content.
I've translated your text using Google Translate:
I wanted to know what exact formula did you use. In fact I tried with IFERROR(INDEX(COLLECT([phone 4]:[phone 4], [phone 4]:[phone 4], <> ""), 1), "") however, I failed to apply it for multiple columns and therefore multiple criteria
I believe you would need to change the range each time you want to pull additional data:
IFERROR(INDEX(COLLECT([phone 1]:[phone 1], [phone 1]:[phone 1], <> ""), 1), "")
IFERROR(INDEX(COLLECT([phone 2]:[phone 2], [phone 2]:[phone 2], <> ""), 1), "")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks for your answer.
Are you sure there is no other method? Indeed, in the discussion above, the telephone numbers as well as the comments could be grouped in a single column.
It's possible to make that ? :
=IFERROR(INDEX(COLLECT({D1+D2+D3}; {Dys D1}; <>""; {Dys D2}; <>""; {Dys D3}; <>""); 1); "")
if it's possible with this formula, the problem is the line because there is more that 1 line per colums.
Thanks
-
Hi @lea.s6
If you have multiple columns and multiple rows, I cannot think of a way to bring all of this data into two columns and have it list down multiple, individual rows.
You could create a helper column in your source sheet that JOIN all the values from each row together:
=JOIN([Phone 1]:[Description 5]; CHAR(10))
Then bring that into a Report to only show the one helper column, but this would show multiple values in one cell.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!