Column Limits vs Data on a Single Row

Sheetuser X

TLDNR (short version)

 

GOAL – everything in reports / sheets to be on one row per project

PROBLEM – I run out of columns and reports won’t merge data from multiple sheets to one row.

 

 

(long version)

 

Typical spreadsheets can present as one project row and all it’s details in columns to the right (scrolling is left to right) or as multiple rows for the same project, with a new row for each new service or category (scrolling is up and down).  

 

I structured our sheets according to services our company performs. Then an owner said he instead wanted sheets structured according to project, with ALL it’s services and details on a single row. Reports would be used to present data in various truncated / streamlined forms with everything on the same row.

 

I attempted this, but ran out of allowable columns, so the “all on one row” for each project isn’t possible until they increase the number of columns they support.

 

I tried to approach it with a sheet for status for all services on one row per project, and another sheet for all details on one row per project (project name and company would be linked between the two sheets).

 

However, when I go to make reports, the data doesn’t merge, i.e.- even though they’re the same project, the report puts data from the status sheet on one row and data from the details sheet on another row with tons of blank spaces between the rows.

 

Is there a way achieve what is desired, i.e.- everything in reports on one row per project for data that’s drawn from multiple sheets?

 

Cell linking isn't the answer because there's the column limit thing, and if we further break down into more multiple sheets to overcome that, then that's further defeating the purpose of sheet consolidation.

 

Thank you.

Andrée Starå

Hi,

You could use JOIN and more to merge multiple columns to one.

Would that work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Sheetuser X

Thanks for the response, but I don’t think so unless I’m reading it wrong, as that would combine data from different cells / columns into the same cell / column.

 

We just want different unique columns from different sheets to appear on the same row of a report.

 

The report generator has the “where?” and “what?” functions, showing all columns from all sheets you select from “where?”.

 

The first time I tried it, it made different rows for the same project according to which sheet they originated from. Now I just tried it again, and it’s combined columns from the “status” sheet and “details” sheet on the same row for it’s project (which is good and what we want), but is leaving various cells blank from the “details” sheet while showing others just fine.

 

I don’t know why it would be doing that; if it’s an error in the software or server, or if there’s something I’m not doing right, but “report builder” seems like a simple, straight forward process I’ve been using fine up to now.

Debbie Sawyer

Hi

Interesting scenario - I don't think Reports will do what you need, as they are not as sophisticated as relational database queries where you can use primary / foreign key relationships to relate the data between sheets.

So, instead of creating a report, are you able to create instead a Grid sheet that uses VLOOKUP commands to look up the unique identifier for the Project in the relevant "other" sheets and then return the columns that you had placed in the report? 

Would this work for you?

Kind regards

Debbie

Andrée Starå

Happy to help!

As Debbie mentioned, VLOOKUP is an excellent option.

Two other options could be cell-linking or a combination of INDEX/MATCH.

Which one that would be the best depends on your structure and use-case.

Hope that helps!

Best,

Andrée

Ezra

I'd consider a pivot table for pulling together project info into a single sheet. Unfortunately, Smartsheet's pivot will stomp over any formulas and hard-code the data... also, numbers will come in as text fields, not values - so graphing the data in a dashboard is not going to work.