Data reference from one sheet to another Formula not working

Cherif Yahia

Hello community,

I was trying to reference some data from one sheet to another via formula but it doesn't seem to work error in cell:

" #unparseable"

, here a breakdown of my goal and the formula I am trying to use:

 have 2 separate sheets, in the main one named request for information where people will submit their request i have set the following columns: ID number. Requester's email. Date of request. Date of answer.

Discipline: A drop-down list containing the available disciplines (e.g., Structure, Architecture, MEP). Recipient email. (here where i need to assign my formula)

Status: A status indicator (e.g., Pending, Approved, Denied)( i will automate through workflow) Note or comment: A space for additional comments or notes.

In the second sheet it is simple with 3 columns first one is ID name, then Discipline: drop down list exactly similar to the one in the request for information sheet, and one more column containing recipient email.

What i need is whenever a requester populate the discipline column in the "Request for information sheet" the recipient email in this sheet will be automatically filled with the email found in the rows of the second sheet named "Discipline List Sheet".

Formula: Vlookup formula

VLOOKUP([Discipline], [Discipline List Sheet].[Discipline]:[Recipient Email], 2, FALSE)

Index formula: (In case i have two recipient in the same discipline)

INDEX([Discipline List Sheet].[Recipient Email], MATCH([Discipline], [Discipline List Sheet].[Discipline], 0)):INDEX([Discipline List Sheet].[Recipient Email], MATCH([Discipline], [Discipline List Sheet].[Discipline], 0) + 1)

Here are 2 pics of my sheet if it can help understand the process.

Request for info sheet:

Discipline list sheet:

Thank you in advance for your help,

Cherif Yahia

Hi, @SSFeatures ,

Thank you for your valuable help, it is solved, i have a small question

in the join formula you have proposed can the emails be generated as contacts in a way when i use a workflow automation notifications can be sent to all the contacts in that cell.

the Index formula I'm using is working but the last part of it doesn't seem to be

=INDEX({Recipient email}, MATCH(Discipline@row, {Discipline List}, 0)): INDEX({Recipient email}, MATCH(Discipline@row, {Discipline List}, 0)+1)

the " +1" part that creates a range starting from the position found by MATCH and ending at the next position. is not working

I was thinking of combining both as follow:

=JOIN(", ", COLLECT({Recipient email}, INDEX({Discipline List}, MATCH(Discipline@row, {Discipline List}, 0)) = Discipline@row))

the purpose is to indicate multiple recipient at the same time so the cell can be used in automation

SSFeatures

Hi @Cherif Yahia,

It's not possible to do this directly within the SmartSheet sheet, however, there are multiple ways to do this using SmartSheet DataMesh, Bridge, and DataShuttle.

This amazing thread from @Brian_Richardson explains exactly how to do it!

https://community.smartsheet.com/discussion/123433/combine-emails-into-a-multiple-contact-column-multi-contact-column

I hope this helps!

SSFeatures

Hi @Cherif Yahia,

Thanks for writing a detailed question and including screenshots, it makes it a lot easier to understand how to help.

One thing that I noticed with your formula is that you use "[Discipline List Sheet].[Recipient Email]" to try to reference the data from the other sheet. However, try creating a cross-sheet reference to reference the data.

Once you create a cross-sheet reference then you can use it to reference the data from different sheets.

One question that I have is, what if you have 10 different people that all have the same Discipline? You don't want to have to use 10 different INDEX formulas to make this work, because that will be a hassle to maintain.

Is it okay if you just have 1 row for each discipline, and you JOIN all of the emails into a single cell?

For example:

Sheet 1

Sheet 2

You can achieve this with this formula:

=JOIN(COLLECT({Recipient Email Range}, {Sheet A Discipline}, Discipline@row), CHAR(10))

CHAR(10) adds a new line in between each email so that it looks nicer in the sheet.

I hope this helps!

Cherif Yahia

Hi, @SSFeatures ,

Thank you for your valuable help, it is solved, i have a small question

in the join formula you have proposed can the emails be generated as contacts in a way when i use a workflow automation notifications can be sent to all the contacts in that cell.

the Index formula I'm using is working but the last part of it doesn't seem to be

=INDEX({Recipient email}, MATCH(Discipline@row, {Discipline List}, 0)): INDEX({Recipient email}, MATCH(Discipline@row, {Discipline List}, 0)+1)

the " +1" part that creates a range starting from the position found by MATCH and ending at the next position. is not working

I was thinking of combining both as follow:

=JOIN(", ", COLLECT({Recipient email}, INDEX({Discipline List}, MATCH(Discipline@row, {Discipline List}, 0)) = Discipline@row))

the purpose is to indicate multiple recipient at the same time so the cell can be used in automation

SSFeatures

Hi @Cherif Yahia,

It's not possible to do this directly within the SmartSheet sheet, however, there are multiple ways to do this using SmartSheet DataMesh, Bridge, and DataShuttle.

This amazing thread from @Brian_Richardson explains exactly how to do it!

https://community.smartsheet.com/discussion/123433/combine-emails-into-a-multiple-contact-column-multi-contact-column

I hope this helps!

Cherif Yahia

Hi @SSFeatures

Many thanks for your help and suggestions.

SSFeatures

You're welcome!