Have 1 cell populate another

Terrapin6908

I have SHEET A and SHEET B; on each, there is a column LAST_NAME.

Is there any way to have SHEET B be populated when SHEET A is filled in?

In other words, if someone enters "SMITH" in the LAST_NAME column of SHEET A, I would love to see it automatically populate the LAST_NAME cell in SHEET B.

Please advise and thanks in advance!

Terrapin6908

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...

Genevieve P.

Hi @Terrapin6908

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

Terrapin6908

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

Terrapin6908

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

Genevieve P.

Hi @Terrapin6908

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

Terrapin6908

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

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

SHEET B

1. SHEET B will see a same row with the (3) cells (Route / WO, Venue ID, Kiosk ID
2. In this sheet, the Venue ID and Kiosk ID are already pre-populated but the Route / WO column is blank
3. 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

Genevieve P.

Hi @Terrapin6908

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?

Terrapin6908

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?

Genevieve P.

Hi @Terrapin6908

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

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

Terrapin6908

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

Genevieve P.

Hi @Terrapin6908

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".

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

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.

4 . Insert the Reference

Now do it again for Reference 2 and Reference 3!

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

6 . Add your own Column instead

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

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

8 . Find this column in Sheet A

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

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

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

Terrapin6908

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

Terrapin6908

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.

Genevieve P.

Hi @Terrapin6908

No problem, I'm happy to help further.

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

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?

Terrapin6908

Now I have this:

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

I get #UNPARSABLE