Variable Cell Referencing Problem

Chris Mazuran

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!!! :)

Andrée Starå

Hi @Chris Mazuran

You can add an auto number column to the source sheet and then the same for the destination sheet and match the rows.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Chris Mazuran

Thank you Andree. How would I match this from sheet to sheet?

Andrée Starå

@Chris Mazuran

Happy to help, but I might have misunderstood your need.

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)