Have 1 cell populate another

I was able to use the "LINK to another cell in another sheet" function to do what I wanted in the first cell. But I need it to apply to the entire column and I need it to reference/match the cells in the other sheet.

For example, If someone enters "SMITH" on SHEET A and the column "ID" = "XYZ", then SHEET B will be populated with that "SMITH" entry - It is very important that this formula references another column of my designation - Hope this makes sense...

Yes! If I'm understanding you correctly, an INDEX(MATCH formula will be ideal. You can set this to be a Column Formula so as rows are added and changed the formula will automatically update.

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value you’re looking to match}, 0))

So in your instance, this would be the formula in the Last Name column for Sheet B:

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

You would need to have that Column ID in both sheets with identical values for the formula to find the match and return the last name value.

Here are some Help Articles I used that you may find helpful: Cross Sheet Formulas / INDEX Function / MATCH Function

Let me know if you need any further help! If so, it would be useful to see screen captures of your two sheets, but please block out any sensitive data.

Cheers,

Genevieve

Would this work the same as: =VLOOKUP([LAST_NAME]@row, {REFERENCE SHEET RANGE}, COLUMN # Value of Return Result=LAST_NAME, FALSE)

So, here are some screenshots. Sheet A looks like...

Sheet B, like this...

So, when someone enters a new row in SHEET B (the Route / WO Column) and the Venue ID and Kiosk ID fields match, the Route / WO value will auto populate onto SHEET A

The end result is to have the Route / WO column auto-populate based on what is entered on SHEET B

Yes, an INDEX(MATCH works in a very similar way to a VLOOKUP, however it doesn't need a range of columns to look at. Instead it evaluates to individual columns.

In looking at your screen captures, it seems like you have 3 values / columns you need to Match to bring back the Customer name though, not just one. In this instance, you can use an INDEX(COLLECT formula to check all three criteria. (See this other Community post).

This is the general structure of an INDEX(COLLECT:

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

So for your formula, something like this:

=INDEX(COLLECT({Route / WO in Sheet A}, {Campaign ID Sheet A}, [Campaign ID]@row, {Venue ID Sheet A}, [Venue ID]@row, {Kiosk ID Sheet A}, [Kiosk ID]@row), 1)

Does that make sense?

Let me know if I've misunderstood what values need to match across the sheets.

Cheers!

Genevieve

Hi and thanks - Those values in the screenshots are only examples. I wish I could explain this better but it's quite simple (in my head anyway ;))

SHEET A

A user will enter data into the row and will fill in all cells
When finished, there will be values we are looking to reference are in the Route / WO, Venue ID, Kiosk ID columns
That row on SHEET A is complete

SHEET B

SHEET B will see a same row with the (3) cells (Route / WO, Venue ID, Kiosk ID
In this sheet, the Venue ID and Kiosk ID are already pre-populated but the Route / WO column is blank
In SHEET B, if the Venue ID and Kiosk ID cells match those same fields in SHEET A, SHEET B's (Route / WO) column will auto-populate with what was entered in the Route / WO column from SHEET A

Hope this is a little clearer

Yes, exactly! In this instance, all you need to do is remove out the additional column & criteria I had added in (the Campaign ID).

The formula above is what you would put into the Route / WO column in Sheet B. It will check the two columns in this current sheet (Venue ID & Kiosk ID), find the match in Sheet A for those two specific values in the same row, and pull through the data in the Route / WO column.

Breakdown of how to structure the formula:

You first list the column in Sheet A you want to pull the value from: {Route / WO in Sheet A}

Then you list the column in Sheet A to look for a match in: {Venue ID Sheet A}

And the cell in this current sheet (Sheet B) that already has that value: [Venue ID]@row

And the second column in Sheet A to look for a second match: {Kiosk ID Sheet A},

And the second cell in this current sheet (Sheet B) that contains this value: [Kiosk ID]@row

Full formula:

=INDEX(COLLECT({Route / WO in Sheet A}, {Venue ID Sheet A}, [Venue ID]@row, {Kiosk ID Sheet A}, [Kiosk ID]@row), 1)

Are you receiving an error with this formula, or a different value than expected?

So I should be able to simply throw that formula in sheet B and it will pull from sheet A? in the Route / WO column?

You can copy/paste the structure of that formula, but you'll need to make sure each of your references is set up properly.

For example, the references [in these] with the @row are looking at columns within Sheet B. You'll need to ensure the column names are identical. See: Create a Cell or Column Reference in a Formula

Then the references {in these} are cross-sheet references. You'll need to use the helper box that pops up when you write a formula to select Reference another sheet and identify each of the columns for each of the three {cross sheet references}. See: Formulas: Reference Data from Other Sheets

Screen Shot 2021-03-17 at 4.36.36 PM.png

Does that make sense? Since you have the helper/example sheet above, why don't you try it out! Let me know if you receive an error, showing me a screen capture of your formula in the sheet itself, and I'd be happy to help further.

Cheers,

Genevieve

Sorry...totally confused. here is the formula I copied into the first blank cell in SHEET B

=INDEX(COLLECT({Route / WO in Sheet A}, {Venue ID Sheet A}, [Venue ID]@row, {Kiosk ID Sheet A}, [Kiosk ID]@row), 1

I get #INVALIDREF

The Invalid Reference error is letting you know that one or all of the cross sheet references, {these references} are not selected correctly.

After you paste in the formula, you will need to delete my text-example of the column you need to reference and insert your own cross-sheet reference by clicking the helper text in the pop-up window.

1 . Delete the text of my first reference {Route / WO in Sheet A}

Click "Reference Another Sheet".

Screen Shot 2021-03-17 at 5.48.35 PM.png

2 . Insert your own Column

This will bring up a pop-up window.

Find your Sheet A in the Pop-Up window, then click the Venue ID Column in Sheet A

Screen Shot 2021-03-17 at 5.48.56 PM.png

3 . Change the Name of the Reference

I always adjust the Sheet Reference Name so that when this pops up in the formula {in these} I know what column it's referencing.

Screen Shot 2021-03-17 at 5.49.08 PM.png

4 . Insert the Reference

Screen Shot 2021-03-17 at 6.01.48 PM.png

Now do it again for Reference 2 and Reference 3!

5 . Delete my Second Reference {Venue ID Sheet A}

Screen Shot 2021-03-17 at 5.49.18 PM.png

6 . Add your own Column instead

Select the correct column, update the Name, click "Insert Reference".

Screen Shot 2021-03-17 at 5.49.56 PM.png

7 . Delete the Final Reference text {Kiosk ID Sheet A}

Screen Shot 2021-03-17 at 5.50.10 PM.png

8 . Find this column in Sheet A

Select the correct column, update the Name, click "Insert Reference".

Screen Shot 2021-03-17 at 5.49.35 PM.png

Then your formula should appear with whatever names you selected for these three references:

Screen Shot 2021-03-17 at 5.50.24 PM.png

I would recommend reviewing the Help Article on creating references as it has a brief video tutorial at the top which you may find helpful: Cross-sheet formulas

Let me know if I can clarify anything further!

Cheers,

Genevieve

I'll try it - Thanks! I will report back

Still lost - Unfortunately. I re-read one of your other posts...

"in looking at your screen captures, it seems like you have 3 values / columns you need to Match to bring back the Customer name though, not just one. In this instance, you can use an INDEX(COLLECT formula to check all three criteria. (See this other Community post)."

I am not bringing back a customer name - sorry.

I need to populate the Route / WO column on SHEET B when the Venue ID and Kiosk ID columns match.

Just can't seem to get this to work.

https://community.smartsheet.com/discussion/comment/283950#Comment_283950

No problem, I'm happy to help further.

This post goes through how to use the Index(Collect formula for the Route / WO column on SHEET B when the Venue ID and Kiosk ID columns match between Sheet A and Sheet B.

When you say you can't get it to work, are you still receiving the Invalid Reference error after you select the column through the "Reference another sheet" link?