Auto Fill from One Column to Another in the SAME Smartsheet

teb

I have two columns. 1. Current Phase. 2. Next Phase. Each column has a dropdown menu of exactly the same choices in exactly the same order listed in the dropdown menu. In an effort to keep this simple, I will use fruit: Apple, Orange, Pear, Grape.

When I select Apple for Current Phase, I want the Next Phase column to auto populate with Orange.

When I select Orange for Current Phase, I want the Next Phase column to auto populate with Pear.

When I select Pear for the Current Phase, I want the Next Phase column to auto populate with Grape.

Each project is in a row. As I add projects, I need to be able to select the phase in the Current Phase column and have it auto populate the Next Phase column what will be the next phase.

First, will Smartsheet do this? Second, if Smartsheet will do this, then how?

Thank you.

Paul Newcome

You need a nested IF statement for this.

=IF([Current Phase]@row = "Apple", "Orange", IF([Current Phase]@row = "Orange", "Pear", IF([Current Phase]@row = "Pear", "Grape")))

Paul Newcome

It looks like you may using Word or some similar program to write your formula. See the slanted quotes there in the third set? See how the first two sets have quotes that are straight up and down? You need them all to be like the first sets.

Try retyping the formula directly in the sheet, here in the Community, or in a text editor such as Notepad.

teb

Yes, I was using Word, in an attempt to make writing simpler since the formula is going to be so long. I appreciate your assistance! I'll give it try.

Paul Newcome

You need a nested IF statement for this.

=IF([Current Phase]@row = "Apple", "Orange", IF([Current Phase]@row = "Orange", "Pear", IF([Current Phase]@row = "Pear", "Grape")))

teb

@Paul Newcome Thank you! I got it to work. I thought there was a problem, but I had left out a closing parenthesis. I appreciate your assistance!

Paul Newcome

Happy to help. 👍️

teb

@Paul Newcome The formula initially worked fine, but as I add on to it, I get the UNPARSEABLE error. My answer choices are longer than apple, orange, pear, grape. I am wondering if there is a character limit, if Smartsheet can handle really long formulas? Thank you.

Paul Newcome

That particular error would not be caused by any of the size limitations. Are you able to copy/paste your formula that is throwing the error directly from the sheet to here?

teb

@Paul Newcome

=IF([Current Phase]@row = "Petition Only: Internal Feedback A", "Petition Only: AC-DC Response Letter Reject", IF([Current Phase]@row = "Petition Only: Internal Feedback B", "Petition Only: AC-DC Response Letter Accept", IF([Current Phase]@row = “Petition Only: AC-DC Response Letter Reject ”, “Reject and Stop”, IF([Current Phase]@row = “Petition Only: AC-DC Response Letter Accept”, “Petition Only: Move Forward”))))

It works great when there are only two answer sets. When I add the third, it stops working. I will be adding over 20.

Thank you for your help!

Paul Newcome

It looks like you may using Word or some similar program to write your formula. See the slanted quotes there in the third set? See how the first two sets have quotes that are straight up and down? You need them all to be like the first sets.

Try retyping the formula directly in the sheet, here in the Community, or in a text editor such as Notepad.

teb

Yes, I was using Word, in an attempt to make writing simpler since the formula is going to be so long. I appreciate your assistance! I'll give it try.

teb

@Paul Newcome New problem, same formula. Not all of the answer sets work. I am getting #INVALID OPERATION, but not always.

Answer set 1 works, but 2 does not. Answer set 3 does not, but 4 does work. Answer set 5 works fine, but then answer sets 6-25, do not. I cannot figure out why. Any ideas?

I am providing part of the formula here and then a breakdown of what does and does not work.

=IF([Current Phase]@row = "Pending", "Not Started", IF([Current Phase]@row = "Petition Only: Internal Feedback A", "Petition Only: AC-DC Response Letter Reject", IF([Current Phase]@row = "AC-DC Response Letter Reject", "Stop", IF([Current Phase]@row = "Petition Only: Internal Feedback B", "Petition Only: AC-DC Response Letter Accept", IF([Current Phase]@row = "Petition Only: AC-DC Response Letter Accept", "Petition Only: Move Forward"

This part works =IF([Current Phase]@row = "Pending", "Not Started"

This part does not work IF([Current Phase]@row = "Petition Only: Internal Feedback A", "Petition Only: AC-DC Response Letter Reject"

This part does not work IF([Current Phase]@row = "AC-DC Response Letter Reject", "Stop"

This part works IF([Current Phase]@row = "Petition Only: Internal Feedback B", "Petition Only: AC-DC Response Letter Accept"

This part works IF([Current Phase]@row = "Petition Only: AC-DC Response Letter Accept", "Petition Only: Move Forward"

After this set, none of the other sets work.