Underlying sheet Linkage

Mounika

Hi,

I have sheet (let's assume it as directory sheet), that captures Institutions names, and in the same sheet I have another column named (for example) " Collaborators" with YES and No, which will not be captured via form (and this column is basically capturing if the institution entered is the one that we are collaboratively working or not?). To capture this field, I have another sheet (let's assume it as institutions sheet), where all the institutions and Collaborators (YES/NO) information was present, and I am planning to link this sheet to directory sheet. I t should work in a way that, as the new institution is being filled in the directory sheet, it should look into the Institution sheet and pulls the Collaborators (YES/NO) info back to the directory sheet.

I am just trying to minimize the number of fields within the form and trying to find workarounds to connect the underlying sheet and pull the information from there based on Institution field.

Please help me find the soultion.

Thanks,

Mounika

Lucas Rayala

Hi @Mounika , it sounds like you need a pretty simple lookup formula. The formula is basically:

=INDEX(COLLECT({Collaborators}, {Institution}, Institution@row),1)

Put this formula in your Collaborators column in the Directory sheet. The {Institution} and {Collaborators} references are cross-sheet references to the corresponding columns in the Institutions sheet. The "Institution@row" reference is a reference to the Institution on the Directory sheet. This is your lookup value and must match exactly with the corresponding value in the Institutions sheet.

Here's some directions on the Index function. This article calls out the usage of the Collect function.

INDEX Function | Smartsheet Learning Center

Lucas Rayala

Hi @Mounika , it sounds like you need a pretty simple lookup formula. The formula is basically:

=INDEX(COLLECT({Collaborators}, {Institution}, Institution@row),1)

Put this formula in your Collaborators column in the Directory sheet. The {Institution} and {Collaborators} references are cross-sheet references to the corresponding columns in the Institutions sheet. The "Institution@row" reference is a reference to the Institution on the Directory sheet. This is your lookup value and must match exactly with the corresponding value in the Institutions sheet.

Here's some directions on the Index function. This article calls out the usage of the Collect function.

INDEX Function | Smartsheet Learning Center

Mounika

Hi @Lucas Rayala

It worked, thank you so much!