Reference Multiple Columns from Another Sheet

Donnax

I'd like a way or formula to pull 9 columns from one sheet and display into another. Cell Linking is not an option as new rows are added daily to the original sheet

Brett Wyrick

Hello!

Could you give a little more detail? I can't quite wrap my head around this use case without something like an example or more explanation.

For clarity, let me know if I at least have this right:

• So you've got one sheet that is regularly getting new rows added - let's call this "Sheet A".
• You want to pull 9 columns worth of data from "Sheet B".

Could you answer these questions:

1. Is the data the same in all 9 columns for each new row?
   1. That is, every time a new row is added, does the data stay the same, or does it change for each new row on the 9 columns?

I'll need more info to see if this is doable or not! Let me know. 👍

Donnax

We want to clone only certain columns to make them visible to an audience. We don't want the audience to see all the columns in the original (A) Smartsheet. We tried cell linking in Smartsheet (B), but it doesn't update when new rows are added.

Looking for a formula that will always see what's in each column of sheet A and display the data onto the other sheet (B) . I have zero experience with formulas and curious if the Index or Match would do this.

Andrée Starå

Hi @Donnax

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Donnax

Thank you for your response. I'm having trouble figuring out what that formula looks like. I have zero experience with formulas and not able to figure it out with any online training resources. If you have an example formula or a good training site or video, that would be extremely helpful!

Brett Wyrick

@Donnax - Here's the documentation from Smartsheet for both Index and Match.

• INDEX: https://help.smartsheet.com/function/index
• MATCH: https://help.smartsheet.com/function/match

Below, I'll try and explain a layman's terms way to do INDEX MATCH - I'd recommend using INDEX/MATCH functions rather than VLOOKUP because INDEX/MATCH functions are more helpful if your data moves around (i.e., if one row gets moved, VLOOKUP can "break").

For your use case, you'll be utilizing what Smartsheet refers to as "Cross-sheet references". You can read about Cross Sheet Formulas (and there's a video) here: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

You'll want to create a Cross-Sheet reference.

So, for this exercise, I'll try and make this make as much sense as I can via forum post and break this out.

----

Example: A Tale of Two Sheets

For this example, we'll have two sheets that have the same info on each, minus one column.

The first sheet we will call "Companies_Private" and the second sheet will be named "Companies_Public".

Here's the data within the "Companies_Private" sheet. Bold & italicized text = Column Name.

____________________________________________________________

Companies_Private Sheet:

Corp‎_____________ │Stock____│ Website│CEO

3M ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ MMM ‎ ‎ ‎ ‎ 3m.com ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎Mike Roman

Adobe ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ADBE ‎ ‎ ‎ ‎ ‎adobe.com ‎ ‎ ‎ ‎ ‎Shantanu Narayen

Alphabet ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ GOOG ‎ ‎ ‎ ‎ google.com ‎ ‎ ‎ ‎ ‎Sundar Pichai

Amazon ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎AMZN ‎ ‎ ‎ ‎ amazon.com ‎ ‎ ‎Andy Jassy

Apple ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ AAPL ‎ ‎ ‎ ‎ apple.com ‎ ‎ ‎ ‎ ‎ Tim Cook

Best Buy ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎BBY ‎ ‎ ‎ ‎ ‎ ‎bestbuy.com ‎ ‎ ‎ ‎Corie Barry

Etsy ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ETSY ‎ ‎ ‎ ‎ ‎Etsy.com ‎ ‎ ‎ ‎ ‎ ‎ ‎ Josh Silverman

Hewlett Packard ‎ ‎ ‎ HP ‎ ‎ ‎ ‎ ‎ ‎ HP.com ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ Enrique Lores

____________________________________________________________

So, for our second Sheet, which we will name "Companies_Public", we just need a sheet with the same columns, minus one (for this example, we'll be removing the "CEO" column - let's say that you didn't want particular users to see that column).

____________________________________________________________

Companies_Public Sheet:

Corp‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ Stock ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎Website ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎ ‎

____________________________________________________________

So, to get this to work, you'll need to utilize INDEX MATCH functions and create some "References" to the "Private" sheet from the "Public" sheet.

---

Create some references on the 'new' sheet to the 'original' sheet

So, first things first, let's create some References.

Go to the "Companies_Public" Sheet.

Right click any cell, and press "Manage References".

This will bring up a pop-up that says "Sheet Reference Manager". Press the "+ Create" button at the top right.

Under "Search for a data source" on the next screen, type in "Companies_Private". Select that sheet. You'll see a snippet of what the sheet looks like within this page:

Select the "Corp" column by clicking the column here, and then rename the "Sheet reference name" to something simple, like "Corp_Column".

Repeat this step for each column ("Stock_Column", "Website_Column"). Make sure you press "Insert Reference" after each time.

---

Link in Cells from 'original' sheet

You'll want to link in the Cell values for the "Corp" value for each row. The "Corp" value is the Primary value for the row. (for more on Primary values, see this link).

To do that, you can right click the cell, then select "Link from Cell in Other Sheet", then find the applicable cell. You can select a range of cells, so for this exercise, we'll be selecting the Corp column range from the "Companies_Private" sheet:

(please note that this will likely link the first 50 rows of the 'original' sheet - you'll need to delete rows that aren't needed once you're through with this setup)

This "Corp" value is going to take the role of our "Identifier" for our INDEX MATCH Functions below. For new rows, we'll cover that later. This is taking care of existing rows.

--------

INDEX MATCH Functions!

We're finally here. Here's how to use INDEX MATCH across several sheets.

Now that you have References set up, now you can run the INDEX MATCH function.

Here's how INDEX MATCH works, in a nutshell:

=INDEX([Range of data to be displayed from], MATCH([Identifier], [Range to look for Identifier], [sort option]), [optional column index])

The Identifier is simply something that you can utilize to match a cell value from one sheet to another.

For this Identifier, you should use a cell value that is always unique (otherwise, if there are duplicate values, this formula will take from the first value it can find).

Here's how the INDEX MATCH pair function works:

• Use the first portion of the INDEX formula to set the range of data you want to display.
• Use the MATCH in the second part of the INDEX formula to designate what row to pull the data from.
• The third portion of the INDEX formula is optional. Use this to specify which column to pull the data from if the first portion of the INDEX formula covers multiple columns. For how we've set this up, you won't need to worry about that.

--------

Formulas for the columns

So, for our "Companies_Public" sheet, here's the formulas for the "Stock" and "Website" columns:

Stock column formula:

=INDEX({Stock_Column}, MATCH(Corp@row, {Corp_Column}, 0))

Website column formula

=INDEX({Website_Column}, MATCH(Corp@row, {Corp_Column}, 0))

For each of these, you'll want to add the formula to the top row, right click the cell then select "Convert to column formula". This adds the formula to the entire column.

To break this down, here's how the first formula works:

• Indexes the referenced "Stock_Column" range from the "Companies_Private" sheet (which will ultimately return the value we're looking for)
• Then finds the row number that matches the name from the "Corp" column of the "Companies_Public" sheet by searching through the "Corp_Column" range of the "Companies_Private" sheet.

Same goes for the formula for the "Website" column.

Basically, what this does is matches the name from the "Corp" field of the row, finds the adjacent value for "Stock", and displays the value of "Stock" from the original sheet.

To test this, you can simply add in the names willy-nilly to see if it'll pull up the correct values for the other columns, and it ought to never give you the error "#NO-MATCH" as long as the value exists. Example of testing:

I added "(Public)" to each column to make it clear that this is from the "Companies_Public" sheet.

--------

I can explain what to do for new rows, but that will have to wait for another time as I'm quite busy at the moment. Hope this gets you and any random Googlers out there far enough on the solution train. 😅

Brett Wyrick

https://community.smartsheet.com/discussion/comment/309421#Comment_309421

Hey Donna.

Re-looking at your request this afternoon, and realized that a Report could do what you're asking here. Does the "Audience" need access to an actual Sheet, or are you just wanting a subset of the data from the original sheet to be available to them? Because a read-only Report would be perfect for that.

Read more on Reports here and let me know if that would possibly work: Reports | Smartsheet Learning Center

Donnax

Hello, they only need access to view information in calendar form. If reports can display as calendar, that would be awesome.

Below is what we want visible versus the original sheet. We publish as Calendar and embed it in a web page.

Brett Wyrick

@Donnax - Got it. That's why you want two sheets. Reports aren't available in Calendar form. Thanks for the screenshots!

Okay, so you could follow my tutorial above, that would work. Let's use "Course Name" as the Identifier.

I'm struggling to figure out how to type all of what you need to do, so figured it'd be easier just to make two "dummy" sheets and share them with you. Links below.

I used the names from your screenshots: "Original_Sheet" and "Visible_to_Audience".

Original_Sheet link: https://app.smartsheet.com/b/publish?EQBCT=962c9006c23240e9b522f32d59e1c15d

Visible_to_Audience link: https://app.smartsheet.com/b/publish?EQBCT=b3fe083ae08f47f58943a6c0db0ac1e0

I added in a "Notes" column to describe what I did for the cells.

----

What I did to make this work was create several References to the "Original_Sheet" from the "Visible_to_Audience" sheet. I created a Reference to each column, which I named "OriginalSheet_ColumnNameColumn" (i.e., "OriginalSheet_CourseDateColumn").

Sheet Reference Manager from the "Visible_to_Audience" sheet.

Then for each cell within the "Visible_to_Audience" sheet, I used INDEX/MATCH formulas like I outlined in my tutorial above.

Here's an example: For the "Course Date" column on the "Visible_to_Audience" sheet, I used this formula and made it a column formula:

=INDEX({OriginalSheet_CourseDateColumn}, MATCH([Course Name]@row, {OriginalSheet_TaskNameColumn}, 0))

Does this make sense?

Looks like you need to figure out what you'd like to utilize as an Identifier for these two sheets to sync up correctly. Then all you'd need to do is create new rows that have the same name as the Identifier, and all the cells would get the correct data.

The "Course Name" on the "Visible_to_Audience" sheet links with the "Task Name" on the "Original_Sheet". That's as far as I could see, so that's the "Identifier" I went with. Looks like your "Visible_to_Audience" sheet has a filter on it which I tried to emulate. It also appears to have different values I couldn't see; so you'll need to figure out how to identify which "Course Name" values to keep within the INDEX/MATCH formulas or a filter of some sort.

After this is set up, all you'll need to do is ensure that the "Identifier" column you choose will be on each sheet. For me, I'm using "Course Name" on the "Visible_to_Audience" sheet and "Task Name" on the "Original_Sheet". You may want to utilize something else.

If you need extra help, I'd be willing to lend a hand for free if you'd like. Feel free shoot me an email at brettwyrick@outlook.com, we can set up a time to chat and set this up.

‎