Cell linking

Carica

i have 2 sheets. Cell linking works fine, but when i want to convert to entire column it send me to index, match formula definitions page.

All i am trying to do is when a cell in column A is populated in sheet 2, to be pasted to a column A of sheet 1.

Hudson_Miller

@Carica

Yes, the sheet2 item number can be anything unique as long as it’s also on sheet1 and is also unique there.
If you can, paste some screenshots of the two sheets and columns in question along with the formula and I’ll see if I can figure out what’s up.

For the circular reference, make sure that the unique value you are using on both sheets are actual values not formulas that pull from somewhere else.

Hudson

Hudson_Miller

@Carica

Great news, glad to help. Please mark this as accepted so others can search for it in the future.

Cheers,

Hudson

Hudson_Miller

@Carica

Greetings -

I'd recommend you set up the index match for sheet 1. Think of cell linking as tunnels and pathways that Smartsheet needs to keep track of in the memory in the sky on a continuing basis whereas index match runs only when the sheet is open.

If you need a hand with the index match formula please feel free to reply back.

Best,
Hudson

Carica

yes, please.

Hudson_Miller

@Carica

The Index function is combined with a Match function to return a specific cell where a match exists between two sheets.

Let's say you have a common tracking field called Item Number. Presuming that Item number exists on both sheets, we would use Item number to look up some other characteristic from the other sheet and bring it back to the destination you are trying to link to. Let's call that other column Size.

So in sheet one we have Item Number and Size. Index match is used to drop the Size information from Sheet two into sheet one.

In Sheet two we have a column called item number and Size and other information.

The formula would look something like this:

=INDEX({Sheet 2 Size Column}, MATCH([Item Number]@row, {Sheet 2 Item Number Range],0))

In words what this formula says is:

Give me the size from sheet two where the item number of this row matches the item number in sheet two and only accept exact matches (the zero).

Hudson

Carica

hmm, thanks!!

I am Getting #circular reference.
These 2 sheets are the same.
All column titles are the same.
I think i understand the concept of your explanation. Sheet2 item number range can be anything unique and matching between the sheets ?

Hudson_Miller

@Carica

Yes, the sheet2 item number can be anything unique as long as it’s also on sheet1 and is also unique there.
If you can, paste some screenshots of the two sheets and columns in question along with the formula and I’ll see if I can figure out what’s up.

For the circular reference, make sure that the unique value you are using on both sheets are actual values not formulas that pull from somewhere else.

Hudson

Carica

its working. Thank you! The source of the issue was how copy ( sheet1 to sheet 2) worksheet workflow was set up. That was in circle, so i added copy helper column to copy when uncheck, and to check when copied.
thank you for the formula!

Hudson_Miller

@Carica

Great news, glad to help. Please mark this as accepted so others can search for it in the future.

Cheers,

Hudson