Variable Cell Referencing Problem

Options

My design is to use a form for an end user to request a code from my smartsheet. Since forms don’t write in rows that are already populated, I added another reference sheet to pull in that code in with an if/then formula when the main sheet sees the right input. 

 So in this example, once the form puts in the 25, the code should be populated from the reference sheet…

 =IF(Value@row = 25, {Gift Code $25 Range 1})

 This works just fine. The problem is that when the form punches in a “25” in cell A2 (which should place the code from row 2 from the referenced sheet), the formula just puts in the original reference cell code, like it was an absolute. Here is another way to look at it…

 What I want to have happen is:

Row 1 = If 25, then code in reference sheet row 1

Row 2 = If 25, then code in reference sheet row 2

 

This is what actually happens:

Row 1 = If 25, then code in reference sheet row 1

Row 2 = If 25, then code in reference sheet row 1 (problem – this should pull code from reference sheet row 2, since it corresponds to row 2 in the master sheet)


I have tried just about everything to fix this, but cant find a reliable solution that allows the form to work and then to populate the codes ad hoc. Help!!! :)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!