cross sheet cell population/formula

Btrombler

Hello,

I'm trying to build a formula that references two sheets, where if the cell @ row is the same name in one sheet (column Study Name) and the 2nd sheet, then the contacts from sheet 2 populate. Here is my current formula, which is saying unparaseable. Any thoughts?

=IF

([Study Name]@row = {Greenphire Study Intake & Tracking Range 1}, {Greenphire Study Intake & Tracking Range 2}@row, "")

Michelle Choate 2

First off - Best practice is to name your references. See my examples below of one way to do it if you still need to reference the sheet.

Second, you could try an INDEX/MATCH instead = INDEX(Greenphire Study Intake Contacts}, MATCH([Study Name]@row, {Greenphire Study Intake Study Name},0))

Michelle Choate 2

There is no way to prevent automatic naming from happening if you do nothing. The only the to do is always remember to change the name. If you accidentally hit it before you did the name, immediately click on the reference again, and select "Update Reference" then it will take you back to where the reference is. Then you rename it then. It SHOULD rename it in your main sheet you are pulling the information in to. It does not always happen, so sometimes you need to refresh before the update appears.

Michelle Choate 2

First off - Best practice is to name your references. See my examples below of one way to do it if you still need to reference the sheet.

Second, you could try an INDEX/MATCH instead = INDEX(Greenphire Study Intake Contacts}, MATCH([Study Name]@row, {Greenphire Study Intake Study Name},0))

Btrombler

Wow the 2nd option worked great—thank you! I hadn't used Index/match before. Can I ask you what you mean by naming my references?

@Michelle Choate 2

Michelle Choate 2

Sure @Btrombler ! I mean when you go to make a cross sheet reference, instead of letting the system name it "Sheet Name Range 1" or "Sheet Name Range 2", you actually call them by the column or cell that you are referencing. So in your instance, if you wanted to keep the Sheet name, I would update my first reference to "Greenphire Study Intake Contacts". If you do not need to keep the sheet name because there is only 1 sheet you will be referencing, then you could just call it "Contacts". Up at the top left when you are creating a reference is where you update the name.

The reason this is best practice is for trouble shooting when you have a huge formula, you know what your 20 references mean, or when you have 20 references in the same sheet, they are easier to use and troubleshoot should something go wrong with the reference, you know what you were intending in the first place from the source.

Btrombler

@Michelle Choate 2 Ahh that makes sense, thank you! But how to you prevent the automatic naming & using your own reference name?

Michelle Choate 2

There is no way to prevent automatic naming from happening if you do nothing. The only the to do is always remember to change the name. If you accidentally hit it before you did the name, immediately click on the reference again, and select "Update Reference" then it will take you back to where the reference is. Then you rename it then. It SHOULD rename it in your main sheet you are pulling the information in to. It does not always happen, so sometimes you need to refresh before the update appears.

Btrombler

Got it, thank you!