How can I pull a report- or write a forumula to show me Rows with a specific status?

gcuringa

I have created a sheet that tracks the training status of 20+ team members on ~200 SOPs. Each SOP has its own row and Team members are the columns. I want to draft a report or pull data into another sheet or easily sort SOPs based on Status for EACH team member. My goal is to be able to facilitate compliance by providing team members with atutomated status reports at a regular cadence: Which SOPs are Yellow and or Red.

I was sort the information using a pivot table in Excel- but this does not address my "automated" criteria. I know SS offers a "premium add-on" - which I don't have and likely will not be able to get. Is there a creative solution I am missing?

Thanks in advance for your help

Andrée Starå

Hi,

I’d recommend starting with a report or multiple reports. If that doesn’t Work we can look at other options.

https://help.smartsheet.com/articles/522214-creating-reports

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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

gcuringa

Hi Andree,

Thanks for your reply but as I mentioned in my questions- reports as I understand them to function do not allow me to pull the data I want. And to be clear- all of my data is in a single sheet. Maybe I am misunderstanding something in your reply?

Andrée Starå

Happy to help!

To me it sounds like a report would work.

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@getdone.se)

Zachary Hall

If you are wanting to pull data into another sheet you could do so using cell references to another sheet. You could consider using an formula like INDEX(A1:B7, MATCH(data to match, A1:B7,0)) in order to pull the data over with A1:B7 being a reference to a range on the original sheet. If you are dealing with thousands of instances you could hit the ceiling, but it is a work around for some of those premium features.

gcuringa

Hi Andree-

I have not found a way using report builder to pull information from Rows. I have 37 columns each one representing an individual team member and ~200 Rows with data. I want to be able pull information about WHICH SOPs are which status for EACH of my 37 columns. I can do this with a Pivot table in Excel- but that doesn't allow me to generate automated reports for my team members. Thanks in advance for any suggestions!

Andrée Starå

@gcuringa

If I don't misunderstand, you could either create multiple reports or transpose the information.

What do you think?

gcuringa

@Andrée Starå

I decided against transposing the data because a sheet with 189 columns does not making accessing the SOP information easy for the team members.

Regarding reports - Can you please explain how I would generate a report for a Team Member column that pulls each row showing a certain status? i.e. all the rows that are yellow for Team Member 1? Within Report Builder I haven't been able to do that.

gcuringa

@Zachary Hall

Thanks for your reply- I have used cell links- but not with formulas- The INDEX looks interesting- I will investigate- Thanks!

Andrée Starå

@gcuringa

For the report, you would select the TEAM MEMBER 1 column in the What? Section and choose the Yellow option.

Make sense?

Would that work?

Also, to add to Zachary's excellent suggestion. The INDEX/MATCH option would probably also work.

I recently developed a solution for a client to show individual information on another sheet with INDEX/MATCH + more, and they don't need to have access to the main sheet.