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.
