Formula Referencing from Another Sheet

Anthony Barthelemy

{Sprints Range 1} - Sprint# Column

{Sprints Range 2} - Req ID Column

{Sprints Range 3} - Issue Key Column

The Sprint Task Column Should Result In Sprint Number: Issue Key. Example Below :

Sprint Task Column Row 1 = Sprint 2: ACR-34

Otherwise it Should Display Not Started

Paul Newcome

Is there a single column on the Main Sheet that has data unique to each row?

Anthony Barthelemy

Yes, the ID Column

Paul Newcome

And can that value be used as a reference to/found on the Reference sheet?

Anthony Barthelemy

Believe so but I think if you take a look at the Excel Formula in the Excel Sheet attached to the Main Smartsheet, it might make things clearer.

Here is what the Formula Looks like in Excel:

=IFERROR(INDEX(Sprints!$A$2:$A$210,MATCH(D6,Sprints!$G$2:$G$210,0),1)&INDEX(":"&Sprints!$B$2:$B$210,MATCH(D6,Sprints!$G$2:$G$210,0),1),

IFERROR(INDEX(Sprints!$A$2:$A$210,MATCH(D6,Sprints!$H$2:$H$210,0),1)&INDEX(":"&Sprints!$B$2:$B$210,MATCH(D6,Sprints!$H$2:$H$210,0),1),

IFERROR(INDEX(Sprints!$A$2:$A$210,MATCH(D6,Sprints!$I$2:$I$210,0),1)&INDEX(":"&Sprints!$B$2:$B$210,MATCH(D6,Sprints!$I$2:$I$210,0),1),"Not Started")))

Paul Newcome

I understand what you are trying to do. Unfortunately Smartsheet doesn't quite function the same way as Excel, so we need to approach it a little differently.

You need to have some data on the Main sheet that is unique to each row that is also on the Reference sheet that is unique to each row there.

Basically the way it will have to look is:

="Sprint " + INDEX({Reference Sheet Sprint # Column}, MATCH([Unique Data Column]@row, {Reference Sheet Unique Data Column}, 0)) + ": " + INDEX({Reference Sheet ACR Column}, MATCH([Unique Data Column]@row, {Reference Sheet Unique Data Column}, 0))

Anthony Barthelemy

Ok, I will use the ID Column for Both Sheets. I will try it later today and let you know how it turn out.

I appreciate all your help.

Anthony Barthelemy

1 Step Closer.

I received a No Match:

="Sprint" + INDEX({Sprints Test Sheet (Reference) Range 1}, MATCH(ID@row, {Sprints Test Sheet (Reference) Range 2}, 0)) + ":" + INDEX({Sprints Test Sheet (Reference) Range 3}, MATCH(ID@row, {Sprints Test Sheet (Reference) Range 1}, 0))

The ID Row is Unique for both Sheets

Paul Newcome

It needs to be unique on each sheet but entered on both sheets. It is what is matched on, so if it is on the Main sheet, but not in the reference sheet, then it will not find a match to pull.

Anthony Barthelemy

Ok, i have the ID 1.1.1 on both Sheets

Paul Newcome

What are your ranges? Both MATCH functions should be the same but in the first you reference Range 2 and in the second you reference Range 1.

Anthony Barthelemy

Ahhh, Got it. Let me correct that.

Anthony Barthelemy

Yes, that worked Perfectly! Thank you very much for all your help

Anthony Barthelemy

Can I add Not Started instead of saying No Match?

Paul Newcome

Yes. You would wrap it in an IFERROR statement.

=IFERROR(original formula, "Not Started")