If/Then copying cell information

I'm sorry if this is posted- I haven't found an example from previous posts that is working for my situation.

I have 2 name columns- [First & Last Name] and [PI First & Last Name], and a third column for 'role'- [Study Role]. If the person picks the role I need (PI) I'd like copy the name of the 1st name column to the 2nd- so that they do not have to duplicate entry; but I need the second to send in an Update Request.

I think the formula is =IF([Study Role]@row = "Principal Investigator", [First & Last Name], BLANK

I don't think the BLANK is right- but with it or with out it i still get a #UNPARSEABLE

Tags:

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hi @Rebecca Atkinson

    Try this variation: =IF([Study Role]@row = "Principal Investigator", [First & Last Name]@row, "")

    You have to designate the row for which the First & Last Name column pulls from. @row will pull the current row's cell. And to leave something blank, you can just put two " marks together.

    I hope this helps!


  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @Rebecca Atkinson

    Instead of BLANK, use "" so formula would look like this:

    =IF([Study Role]@row = "Principal Investigator", [First & Last Name], "")

    I hope this helps,

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Well. It kind of worked. It created another problem- and is not carrying the formula in new entries on the next row.

    My new problem is if the role is any of the other options i have listed, i want the name that the form collects to stay. Basically only bringing in the name if one role (PI) is the one submitting the form. so it will be an either or.

    This and the formula does not apply to the next row when collected via form.

  • OK!!! solved problem B- converting the formula to the entire column. now working on problem A still.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    @Rebecca Atkinson Could you post a screenshot of what you're referring to? Where are you putting this formula? It should go in the PI First and Last Name. Does your form give people the option to select another name? If so, you can't combine a dropdown row and a formula in one row. You might be able to add an additional column to automate it but I guess I'm not too positive what the request is. A screenshot would definitely help.

  • So if someone says they are the Principal Investigator- I want a formula to put their name into the column PI First Last- so they do not have to duplicate.

    If they indicate their role is anything else- Co-I, etc. I have logic built in the form that the PI First Last opens and they can input the name. If the role is Co-I, etc. I want whatever they imputed in the form to stay in the PI First Last column.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    What I would do is have a separate (hidden) column that the form fills out for people who are registering as Co-Investigators then have the formula check that hidden column for information... For instance,

    If you create a column called PI-FORM and have the form populate that column instead, then you could do the following:

    =IF([Study Role]@row = "Principal Investigator", [First & Last Name]@row, IF([Study Role]@row = "Co-Investigator", PI-FORM@row))

    IF those are the two roles, then doing this would resolve your issues.

  • Bingo! Makes sense! Thanks- this is another way I thought about doing it, but was not sure how to do that either- the hidden row was the key.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome. Glad I could help you figure it out.

    Please accept whichever answers you thought were helpful in getting you to the right place! I appreciate it!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!