Can I pull columns in different Sheets in one report as long as Primary Column is the same?

MMcLain · 09/28/23

Dear All:

I tried the Community and I came up with nothing. Can I pull different columns in different Sheets in one report as long as the Primary Column is the same?

The columns I need exist in different Sheets, not all Sheets have all the columns. However, the Primary Column is the same (“Interpreter Name” as Text/Number, for example Jane Doe).

For this project, I don’t want to use Copy Rows because I need different things from 4 types of Sheets, by the time I get everything I need, a Sheet will have hundreds of columns.

Since new data will continue to be added to those Sheets, setting up different reports then copy what I need to a data source sheet may require too much manual work than I’d like.

For example:

Jane Doe is listed in 2 different Sheets (Primary Column set as Text/Number)

I need the Columns A to F from one Sheet (This Sheet doesn't have Columns I - M ), and Columns I - M from another Sheet (This sheet doesn't have Columns A to F)

I also read about the API approach, can a non Smartsheet Account Admin use API directly?

Thank you in advance.

Megan

Genevieve P. · 10/02/23

Hi @MMcLain

Think of a Report as like a window to your underlying sheets. It does not combine rows together or merge content, but it can bring into one view rows across multiple sheets.

For example in your scenario, Jane Doe would have 2 rows in a Report. One row would have blank cells in columns I - M, and the other would have blank cells in columns A - F, since those columns don't appear in those Sheets.

For example, here is Sheet 1 vs Sheet 2:

See how I can't edit cell "E" because my Sheet 1 does not have this as a column:

However you could use the Report to Group by your Primary Column so you see all rows together, under the name as a heading. Here's an example:

Here's a free webinar that goes through Grouping and Summary: Redesigned Reports with Grouping and Summary Functions

An alternative is to use formulas, like cell-links, to bring through data from Sheets 1 - 4 into a master sheet, linking only the relevant information. Here's an article that explains a few different formulas you could use: Formula combinations for cross sheet references

Cheers,

Genevieve

Genevieve P. · 10/03/23

Hey @MMcLain

No problem at all!

So for a formula, I would have one sheet that lists all the unique IDs. In my example above it's the Name. Then I would use an INDEX(MATCH combination to search through Sheet 1 to find the same name and bring back certain cell data:

=INDEX({Sheet 1 Column A}, MATCH([Name Column]@row, {Sheet 1 Name Column}, 0))

You would need to do this for each column you want brought over, changing out what column you're indexing in the front of the formula for what to bring back:

=INDEX({Sheet 1 Column B}, MATCH([Name Column]@row, {Sheet 1 Name Column}, 0))

=INDEX({Sheet 1 Column C}, MATCH([Name Column]@row, {Sheet 1 Name Column}, 0))

Then when you get to a column that's in a different sheet, delete out the references and create ones that look at the next sheet instead:

=INDEX({Sheet 2 Column D}, MATCH([Name Column]@row, {Sheet 2 Name Column}, 0))

=INDEX({Sheet 2 Column E}, MATCH([Name Column]@row, {Sheet 2 Name Column}, 0))

=INDEX({Sheet 2 Column F}, MATCH([Name Column]@row, {Sheet 2 Name Column}, 0))

Here's more information:

https://help.smartsheet.com/articles/2482647

Cheers,

Genevieve

Genevieve P. · 10/02/23

Hi @MMcLain

Think of a Report as like a window to your underlying sheets. It does not combine rows together or merge content, but it can bring into one view rows across multiple sheets.

For example in your scenario, Jane Doe would have 2 rows in a Report. One row would have blank cells in columns I - M, and the other would have blank cells in columns A - F, since those columns don't appear in those Sheets.

For example, here is Sheet 1 vs Sheet 2:

See how I can't edit cell "E" because my Sheet 1 does not have this as a column:

However you could use the Report to Group by your Primary Column so you see all rows together, under the name as a heading. Here's an example:

Here's a free webinar that goes through Grouping and Summary: Redesigned Reports with Grouping and Summary Functions

An alternative is to use formulas, like cell-links, to bring through data from Sheets 1 - 4 into a master sheet, linking only the relevant information. Here's an article that explains a few different formulas you could use: Formula combinations for cross sheet references

Cheers,

Genevieve

MMcLain · 10/03/23

Thank you so much @Genevieve P., for the detailed explanation! I was hoping merging data from different Sheets was possible. I also made a mistake before my initial post: I used filters that would only apply to one of the Sheets.

I have used cell-links when I work on small datasets but I will have thousands of rows (and new ones will be added), I think I will need to use cross sheet reference. would you please show me the formula you would use to bring the data together in your example together? Thank you again for sharing your insight and for your time!

Have a great Tuesday!

Genevieve P. · 10/03/23