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.

Find more posts tagged with

Formulas

Comments

Andrée Starå

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

c_griffin_DR

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

Paul Newcome

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.

Andrée Starå

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…