Formula to display multiple rows
Hi, I am looking for a formula that would allow me to display a list of projects based on a client.
Ideally, what I want is to have a dropdown column with a list of all the clients. When a client is selected then a list of their projects will display. This sheet would link to a master sheet of all clients and all projects. I was able to create something similar in excel before but it was a very complicated formula that does not work in Smartsheet.
I dont want to use a filter on the master sheet or a report. The audience for this is the Senior Leadership team so I want to make it as easy as possible for them to view the list of projects by client without having to mess with filters or report builder. I also dont want to have to create a sheet or report for each client as we have nearly 50 of them.
Any help would be appreciated.
Comments
-
Hi,
How many rows and columns can there be in the Master and how many of the columns do you want to show in the project selection sheet?
Hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
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,
There are 26 columns in the Master sheet, however from the first column I would need to the last column I would need it is 12 columns so I wouldn't need a formula that includes all 26. There are 155 rows at the moment but that will increase but should not go over 250.
I need 6 of the columns from the Master sheet for my project select sheet.
Thanks,
Catherine
-
I am thinking something along the lines of a dashboard solution.
You could create a sheet that is a comprehensive listing of all clients. Include a checkbox.
On the Projects sheet, include another checkbox column. Using cross sheet formulas, you can have the boxes check on the project sheet if the box on the client sheet is checked.
Basically this would give the capability of being able to check the box for one or more clients on the client sheet and the project sheet would automatically flag each project for whichever client(s) were selected.
Then create a report that looks at the project sheet and pulls rows that have this new box checked.
.
Display the Client Selection sheet on a dashboard alongside the Project report.
.
Check your boxes on the Client Selection Sheet. Save. Refresh browser (or wait for the dashboard to auto-refresh which can be set for every 60 seconds). Then the report will be updated with the projects for the selected clients.
Using the dashboard keeps the client selection and report listing on the same screen, and you can even include a text widget that has the very basic instructions on how to use this dashboard.
-
Happy to help!
I might have another option for Paul's excellent solution below. Let me know if you need it or if Paul's method will work.
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!