If/Then copying cell information

Rebecca Atkinson

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

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!

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell#toc-use-row-to-improve-formula-performance

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

Rebecca Atkinson

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.

Rebecca Atkinson

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

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.

Rebecca Atkinson

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

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.

Rebecca Atkinson

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

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!