How to Use Data from One Sheet in Another with Cross-Reference List and Index Formula?

System

This discussion was created from comments split from: How to Index Match.

Vika Velickaite

Dear all,

I am really desperate with trying to use data from one sheet in another and I can't understand how that works.

I have main file (source) where my colleagues are inserting new rows. I have other sheets where I need to use only two columns. I prepared cross-reference ListName and ListDepartment, but how I can prepare index formula in other sheet if I want two first column be dublicated from the source sheet and new rows to be added at the same place as in source sheet? Maybe someone can help me with this case.

Pauline J

Hello @Vika Velickaite

I didn't see the previous posts, but … it sounds similar to a project I manage where I connect to another department's sheet, and need to pull in the data. It was a little bit to setup, but they agreed to create an autonumber column, giving us a unique identifier for each row. Then, in my sheet, I setup the same column, replicating their numbering format. This way, every row in their sheet has the same unique idenfier in my sheet rows.

In the sample image, I included their column number in my Column name. You can see that I didn't need all of their data, just some of it.

The formula (converted to column formulas)

=VLOOKUP(RowID@row, {NameOfTheirSheet Range 1}, 3, 1

The range is their entire smartsheet. The number "3" is the number of the column, from left-to-right, that will populate your sheet.

You have to have "empty" numbered rows in your sheet so that when they add new rows, their new rows will match into yours. I just added about 300 rows, which will last me a long time.

Example:

The other important thing, is that the owner of the sheet you are pulling from cannot alter the order of the columns! Because Column number 3 is just that — if Column 5 moves over to become Column 3, you'll get Column 5's cell contents — and all the rest of your columns will "slide" as well. What my collegues do is add new columns if they are needed, to the far right of the sheet, so the column numbers do not change.

I've also done the same thing but using index/match:

=IFERROR(INDEX({TheirFile Range 1}, MATCH([Row ID]@row, {TheirFile Range 2}, 0)), " ")

Each column also has a unique formula, because the Range 1 is the column you want to return into your column. BOTH methods require that original file columns stay in order.

I hope this makes sense — and I hope it helped! If someone else has a better solution, I'd love to learn about it.