Replicating a Excel formula in Smartsheet

Wendell ONeal

I am trying to replicate a formula in Smartsheet from an Excel formula. However, Smartsheet is returning an #INVALID REF error. I have attached a picture of the formula in both formulas. ANy guidance would be appreciated.

EXCEL

=VLOOKUP(D2,Score!$A$1:$B$4,2,FALSE)+VLOOKUP(F2,Score!$D$1:$E$5,2,FALSE)+IF(OR(C2="Signed",C2="Confirmed"),50,0)+IF(E2="New",25,IF(E2="Critical",50,0))+IF(J2="Yes",20,0)+IF(I2>30,20,0)

Smartsheet

=VLOOKUP([Customer: A, B, C, D]@row, {"A",25; "B",10; "C",5; "D",0}, 2, false) + VLOOKUP([Health Status]@row, {"Green",0; "Yellow",10; "Red",35; "New",50}, 2, false) + IF(OR([Contract Status]@row = "Signed", [Contract Status]@row = "Confirmed"), 50, 0) + IF([Install Type]@row = "New", 25, IF([Install Type]@row = "Critical", 50, 0)) + IF(Rescheduled@row = "Yes", 20, 0) + IF(Age@row > 30, 20, 0)

Thank you.

Michelle Choate 2

#INVALID REF means one of those references from another sheet are not working. Either you grabbed a single cell instead of a column, or it did not create correctly. So I would look at: {"A",25; "B",10; "C",5; "D",0} or {"Green",0; "Yellow",10; "Red",35; "New",50}. I would also check to make sure that the source sheet has information, as does the destination sheet.

If either are blank, then you will receive that error as well. You can surround your entire formula by IFERROR.

=IFERROR(VLOOKUP([Customer: A, B, C, D]@row, {"A",25; "B",10; "C",5; "D",0}, 2, false) + VLOOKUP([Health Status]@row, {"Green",0; "Yellow",10; "Red",35; "New",50}, 2, false) + IF(OR([Contract Status]@row = "Signed", [Contract Status]@row = "Confirmed"), 50, 0) + IF([Install Type]@row = "New", 25, IF([Install Type]@row = "Critical", 50, 0)) + IF(Rescheduled@row = "Yes", 20, 0) + IF(Age@row > 30, 20, 0), ""

That will remove the error altogether and the cell will display nothing if there is no information.