Reporting from two completely different sheets

I have inheritied two sheets that have the same primary column (project name) but the rest of the columns are completely different. Is it possible to combine parts of these into a summary report? I want the report to have one row for each project listed in the primary column. In my current attempt the projects show twice and the information is displaying partially in each of the two rows, rather than combining into one row.

Find more posts tagged with

Reports

Comments

Richard Rymill SBP

Faith, I think you have taken that as far as you can using the sheets as they are, you could add some extra filters but I doubt that will satisfy you.

What I suggest is you combine the two sheets into one, consolidate the data in them if possible to reduce duplication of similar column info.

To do this easily, in sheet 1 select all the Rows you want to copy to the other Sheet 2 choose "copy to another sheet" choose sheet 2, then when the data copies over, any columns that exist in Sheet 1 but not in Sheet 2 will be created, so you have a combined sheet. If you need to De-dupe then do so at that point.

With the combined sheet you can use Filters or Reports as you wish to see what matters to you.

Hope that Helps?

RichardR

TrishK

Hi Faith,

Did you ever find a solution to your question? I am experiencing the same thing, but do not have the option to copy and paste to combine the sheets. My client uses smartsheet and has shared several of their sheets with me. I'd like to compile the information as well as be able to connect it with the sheets I use internally. We all have a common project number, but currently anytime I draw from more than one sheet I get multiple rows with the same project number and different columns filled in instead of one row with all the columns filled in.

Any advice would be much appreciated!

Richard Rymill SBP

TrishK

Now we have the benefits of Cross sheet formulas which we did not have last year, I feel a different methodology of consolidating data may be possible for you?

You also need to insert some Data triggers to pull up the data that interests you and ignore what you don't want. Possible a dashboard or two may be helpful at the least for links to relevant sheets, if not to actual KPIs?

It is almost certainly worth sending some time in understanding your workflows so you can make changes which will make this work a whole lot easier. My colleagues and I do exactly this for 100s of Smartsheet users if you need that support, but there is a cost, so you will need some budget for that?