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

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…