Have 1 cell populate another

Genevieve P.

https://community.smartsheet.com/discussion/comment/283969#Comment_283969

This is great! You're almost there. You're receiving an error because you have double these {{ instead of just single {.

When you insert a new cross-sheet reference it automatically adds in {these}. You will want to ensure there's only one set around each range, not two.

=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)

Terrapin6908

Here's what I have now:

=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)

Now, there are really no matching values entered yet as SHEET A still needs to be completed by others. So I get an #INVALID VALUE in the cell.

Will this rectify itself once values are populated?

Genevieve P.

@Terrapin6908

Yes! It should!

If you want to get rid of this message, you can add an IFERROR Statement around it, like so:

=IFERROR(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), "No Match")

This will say "No Match" if it can't find a match.

Terrapin6908

I'll see if this starts to work and let you know - I truly appreciate all the help with this one

Genevieve P.

It's my pleasure 🙂

Formulas feel like magic when they work properly. Please do let me know if there's anything else that comes up.

Cheers!

Genevieve

Terrapin6908

Looks like it is working!!!!!! Can't thank you enough!

Terrapin6908

By the way - If I want this to reference one more sheet - where would I add that?

Terrapin6908

Like SHEET C - So I would want SHEET A and SHEET C feeding into SHEET B

Terrapin6908

Another thing I want to do with our original formula is to exclude any entries that contain, say, ABCDE in the Route / WO column - how would I add that exception?

Genevieve P.

Hi @Terrapin6908

https://community.smartsheet.com/discussion/comment/284007#Comment_284007

If you have a different column that you want to use to look into Sheet C, then you can re-build the exact same formula. You will need to create entirely new cross-sheet reference ranges {in these} that look specifically into Sheet C.

However if you want this data to feed into the same cell, you could add two formulas together.

Each formula can only look into one sheet. You would do something like this:

= Formula Sheet A + " / " + Formula Sheet C

Can you clarify what you're looking to do with sheet C in more detail? If you have multiple sheets it may be better to create a Report from Sheet A and Sheet C to view all filtered data. See: Create a Report

https://community.smartsheet.com/discussion/comment/284016#Comment_284016

For this comment, can you clarify what you mean? You can use a filter on the sheet to hide rows with certain criteria, or you could use a Report (as noted above) to only bring in specific rows.

The INDEX(COLLECT formula will copy over data if it finds a match in the other two columns. What if Sheet B has matched Venue IDs and Kiosk IDs for the "ABCDE" value in the Route / WO column in Sheet A or C? Do you want Sheet B to show a blank cell, or some sort of text response like "Blocked Data" to hide the value?

Terrapin6908

Let's start with this:

Working with our original formula,

=IFERROR(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), "No Match")

Everything stays the same except I would like to NOT include (populate SHEET B) any entry in the Route /WO column (on SHEET A)that contains, 12345, for example.

Hope that makes sense.😉

Genevieve P.

Hi @Terrapin6908

No problem, we can embed this formula into an IF statement then.

The statement would say, check the sheet for a match. If the match happens to bring back "12345", then say "Blocked Data" instead of bringing through that value. Otherwise, if the match finds any other value, bring back that match.

=IF(formula = "12345", "Blocked Data", formula)

This says, if the formula brings back 12345, say "Blocked Data", otherwise, run the formula.

Put together:

=IF(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) = "12345", "Blocked Data", IFERROR(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), "No Match"))

This won't prevent the sheet from populating if there's a match, it will just change what that value is in your second sheet. It changes it from saying "12345" to saying "Blocked Data". Is this what you were looking to do?

Terrapin6908

Perhaps - I am sure I am not explaining this correctly. Seems simple, but, here's an example.

The ROUTE / WO, Venue ID and Kiosk ID columns, in the source sheet-SHEET A may look like:

And the SHEET B space, the sheet where we are pulling data into, like this:

I would want to exclude the entry "John Smith ABC998..." from coming over to SHEET B and, in fact, any entries containing ABC even if there is a match on the Venue ID and Kiosk ID fields -

Terrapin6908

Remember, the field which I am populating on SHEET B is the Route / WO field ;)

Genevieve P.

Hi @Terrapin6908

Yes exactly! That's what the formula would do. You would just need to specify the value you want to block out, and what you want it to say instead of that value. Do you want a blank cell in row two, instead?

=IF(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) = "John Smith ABC998 Las Vegas NV", "Blocked Data", IFERROR(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), "No Match"))

Will there only ever be the one value you want to blank out, or are there many potential values you're looking to filter? If there are many values, is there a common theme? (Ex. they all say "ABC").