Create a report from multiple sheet but with row as Columns

Ben2popu

hi Friends,

I am trying to make my Smartsheet work better for my user. the problem that I am facing id the following

My Workspace is build in the following way. Each of our project have a specific folder name let say Project1 , Project 2, Project 3

In each of that folder we have many many files, one of them there is a sheet called Project - main sheet that user update on a daily basis

the sheet look like this and have the same format in each folder

The issue that we are facing is that in order to update the data they need to enter the specific folder Open the specific sheet update it ... it takes tons of time

We would like to create a report where each row is the Sheet Name, the columns are the SCC MetaData Fields and the value to input, the text value

How can I achieve this ?

Thx you

Ray Lindstrom

Hi @Ben2popu,

You can create a column in your Projects called Project Name. I would hide the column as it won't provide much value when viewing the grid. You can populate it 3 ways:

1.) use automation to change cell value for any time a new row is added, and have it insert the project name.

2.) if you have your project name in line 1, then use a cell reference in the Project Name column =[Task Name]1

3.) add a Summary field called Project Name, and use a cell reference in the Project Name column =[Project Name]#

For options 2 & 3; after inserting your formula into a row, convert it to a column formula.

Then in your reports, include the column for Project Name and point the report to as many projects as you like. When the report is populated, you'll have a column to identify which project each task/row belongs to.

Hope this helps!

BRgds,

-Ray

Ben2popu

I am not sure to understand your recommendations ;(

where should I add a columns project name. ? in my Project - main sheet ?

Then in your reports, include the column for Project Name and point the report to as many projects as you like. When the report is populated, you'll have a column to identify which project each task/row belongs to. ==> I am not sure to understand ;(

Ray Lindstrom

Hi @Ben2popu,

Yes, add a column called "Project Name" to each of your project plans.

When creating Reports, you must choose which columns to display. Make sure you include the "Project Name" column in Reports that you will be pointing to multiple project plans.

Here's how I do it:

You have 3 projects and a Report.

Each of the project folders has a project plan named "Project".

Here is what the project plan for Project 1 looks like. It has a column that uses automation to enter the project name for every row. I would hide the "Project Name" column in your project plans. I didn't hide it here, so that you could see it.

Now go into your report, and select the 3 sheets for the projects.

Select your columns, and be sure to include "Project Name". Don't include "Sheet Name". That won't help any.

Set your filter criteria. I filtered to tasks that I'm assigned to (which in this case is all of the tasks).

I like to drag the "Project Name" column, so that it appears as the first column.

I also like to double click the Primary column, and change it to "Task Name" as it's named in the project plans.

Here's what it looks like with all that.

Also, you can optionally choose to Group by "Project Name", and even Sort by "Project Name". In my final screenshot just above, I opted to Sort Ascending by "Project Name"

Hope this clears it up a bit. Best of luck to you!

-Ray