Auto Fill Fields in Sheet

Jessica Langston

Hello,

I have 2 columns I am looking to auto populate based on what is selected in another column.

Depending on the requestors name the Manager and Director will populate. The thing I am having trouble with is that multiple requestors are under a manager. I cant get my IF statement to work.

Example:

Lisa, Doug, and Frank are all under Michael as the Manager.

I can get =if Requestor is Lisa then add Michael to the column, but I cant get multiple selections... Also, all fields are Contacts.

The purpose is to be able to remove those two fields from the form.

Andrée Starå

Hi Jessica,

What formula are you using now?

Can you describe your process in more detail and 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, [email protected])

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help 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!

Paul Newcome

Additionally... How many different names will you need to account for? It may be more efficient to build out a table and use an INDEX/MATCH formula to pull from the table as opposed to a long nested IF statement.

It is also not currently possible to populate multiple contacts into one cell via a formula. I am not sure if that is what you were referring to when you said "I cant get multiple selections" or if you had tried unsuccessfully and did not know why.

Jessica Langston

Andrée Starå

Hi Jessica,

What formula are you using now?

Can you describe your process in more detail and 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, [email protected])

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help 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!

Paul Newcome

Additionally... How many different names will you need to account for? It may be more efficient to build out a table and use an INDEX/MATCH formula to pull from the table as opposed to a long nested IF statement.

It is also not currently possible to populate multiple contacts into one cell via a formula. I am not sure if that is what you were referring to when you said "I cant get multiple selections" or if you had tried unsuccessfully and did not know why.

Jessica Langston

Thank you for your comments. After posting I watched tons of YouTube videos and saw that a Vlookup to another table was the easiest.

The columns are populating appropriately depending on the requestor field. I am now noticing that dragging the formula down is creating blank rows. so I do a form submission, and the fields are populating, but once I delete the "test" the formula is no longer there.  Do I have to leave my test submissions in order for the formula to continue throughout the sheet?

Paul Newcome

For auto-fill, you need to have at least 2 rows of the same hierarchy and containing the formula above and/or below the new row.

You would not drag the formula down. It sounds as if your forms are populating at the bottom of the sheet, so you would leave two "formatting" rows at the top of your sheet. The only columns that NEED to have anything in them are those columns containing the formulas you want to auto-fill.

I generally fill the rest of the columns with "FORMATTING ROW - DO NOT DELETE OR EDIT" if other people are going to be accessing the sheet directly.

Once you have had at least two form entries populate that you are not going to delete for the sheet, you can then delete your two formatting rows and the formula should carry on.

Jessica Langston

@Paul Newcome

That was EXTREMLY helpful. Thank you SO much!

Paul Newcome

Happy to help! 👍️

Andrée Starå

@Jessica Langston,

I saw that Paul answered already!

Glad you got it working!

Let me know if I can help with anything else!

Best, 

Andrée