Formula or Rules to AutoPopulate Columns in Sheet

Jo-Ann Turning

I have a master data sheet where a team of 30+ people will be adding 2021 data and creating our annual plan.

To make it easier for them to complete the sheet, I wanted to have the ability to autopopulate certain columns based on their answer from a drop down choice in an earlier column.

As an example:

1) If they select "Specific Product Name" as a drop down choice, I want to autopopulate "Therapeutic Area" and "Business Area" and "Sub Business Area" columns automatically.

2) If they select "Program type" as a drop down choice, I want to autopopulate "Program Weight" category.

etc....

I wasn't sure if that was done using a formula, automation or rule, or if I had to cross reference a separate data sheet.

I have been searching the forum and don't seem to find this specific situation.

Thank you for your help!

Andrée Starå

@Jo-Ann Turning

Try something like this. Continue the pattern for each option.

1. =IF([Program Type]@row = "Advisory Board", 1,
2. IF([Program Type]@row = "Value", Value,
3. IF([Program Type]@row = "Value", Value,
4. IF([Program Type]@row = "Value", Value)

Make sense?

Working formula for two options.

=IF([Program Type]@row = "Advisory Board"; 1; IF([Program Type]@row = "Ad Board teleconference"; 0,5))

The same version but with the below changes for convenience.

=IF([Program Type]@row = "Advisory Board", 1, IF([Program Type]@row = "Ad Board teleconference", 05))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Andrée Starå

@Jo-Ann Turning

You're more than welcome!

Try this one. (You were using [] brackets. It should be "" for the text.

=IF([BoB Country]@row = "US", "HQ", IF([BoB Country]@row = "GHQ", "HQ", IF([BoB Country]@row = "GEMS Reg AP", "REG", IF([BoB Country]@row = "GEMS Reg EMEAC", "REG", IF([BoB Country]@row = "GEMS Reg LA", "REG"))

Did it work?

Andrée Starå

Hi Jo-Ann,

You could use multiple IF functions. How many different options can there be?

Also, would the auto added values need to be changed?

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!

Jo-Ann Turning

There would only be one option tied to each selection.

If they selected "Advisory Board" in the Program Type drop down, I want "1" to show up in Program Weighting column.

If they selected "Ad Board teleconference" in drop down, I would want ".50" to show up in Program Weighting.....

each drop down choice would have a weighting associated with it....

I have 11 different program types as possible drop down choices - each one has a "weighting" assigned to it.... so if I use the "multiple IFs" function do I just keep writing the same formula over and over? that's where I get confused.....

thanks for your quick reply!

Andrée Starå

@Jo-Ann Turning

Try something like this. Continue the pattern for each option.

1. =IF([Program Type]@row = "Advisory Board", 1,
2. IF([Program Type]@row = "Value", Value,
3. IF([Program Type]@row = "Value", Value,
4. IF([Program Type]@row = "Value", Value)

Make sense?

Working formula for two options.

=IF([Program Type]@row = "Advisory Board"; 1; IF([Program Type]@row = "Ad Board teleconference"; 0,5))

The same version but with the below changes for convenience.

=IF([Program Type]@row = "Advisory Board", 1, IF([Program Type]@row = "Ad Board teleconference", 05))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Jo-Ann Turning

Thank you!!! that helped so much. Does the same formatting apply if my value that I want to show up in the second column is text?

I was trying to follow the same logic for some other column references but it shows as #UNPARSEABLE

Here was the logic I'm trying to use:

US = HQ

GHQ=HQ

GEMS Reg AP=REG

GEMS Reg LA=REG

GEMS Reg EMEAC=REG

here was the formula I tried to do following that same logic? Do you see anything I'm missing? If I have the answer to this question - I think you will solve ALL my problems for this project sheet. Thank you!!

=IF([BoB Country]@row = "US", [HQ], IF([BoB Country]@row = "GHQ", [HQ], IF([BoB Country]@row = "GEMS Reg AP", [REG], IF([BoB Country]@row = "GEMS Reg EMEAC", [REG], IF([BoB Country]@row = "GEMS Reg LA", [REG]))

Andrée Starå

@Jo-Ann Turning

You're more than welcome!

Try this one. (You were using [] brackets. It should be "" for the text.

=IF([BoB Country]@row = "US", "HQ", IF([BoB Country]@row = "GHQ", "HQ", IF([BoB Country]@row = "GEMS Reg AP", "REG", IF([BoB Country]@row = "GEMS Reg EMEAC", "REG", IF([BoB Country]@row = "GEMS Reg LA", "REG"))

Did it work?

Jo-Ann Turning

Thank you!! thank you!!😀 yes this worked. And I figured out how to accomplish also using VLOOKUP...

Andrée Starå

@Jo-Ann Turning

Excellent!

You're more than welcome!