Auto populate in multiple blank rows when a another cell dropdown is changed?

Morris R

Hello and thank you

I have a sheet that has specific milestones to be met based on each selected project size option. I am hoping to automatically shows those milestones based on the size selected in a drop down for the next blank rows.

For example:

"Small" has 3 milestones , "medium" has 6 milestones, "large" has 10 milestones.

Based on the size of the project I wanted the subset of milestones to populate in the subsequent rows below the header.

So if i select "small" all 3 milestones show automatically in 3 rows, "medium" in the next 6 , or "large" in the next 10 - Since those milestones are evergreen and don't change.

Thank you

Mike TV

@Morris R

Let me know if this achieves what you're trying to do. Here's a sample database sheet I created first. You list out all of your possible Sub-Tasks for the different Project Sizes like so:

Then you create your lookup sheet. If Large is your biggest Project Size and it has 10 Sub-Tasks, for example, then you'll need to pre-fill a formula into 10 spaces where the Sub-Tasks will automatically be listed. I set up my working sheet like so:

Each of the 10 formulas which gathers the Sub-Task names are pre-filled into the sheet with a formula such as:

=IFERROR(INDEX(COLLECT({Project Size Database Sub Task Name}, {Project Size Database Project Size}, [Project Size]1), 1), "")

You only need to change the number near the end of the formula right before the double quotes ("") near the end. For the first row it'll be a 1, the next row it'll be a 2, and so on. You don't have to create multiple instances of this like the 2 I've shown above. You can just create the one set if you want. Where you see the Project Size on the 2nd sheet that gathers the Sub-Task names, you can make that a drop-down box if you like with the different Project Sizes to select from. When you change that cell to a different Project Size it will re-fill the Sub-Tasks with the appropriate ones for that Project Size.

Andrée Starå

Hi @Morris R

I hope you're well and safe!

Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Morris R

Yes thank you

My project sizes change based on scope but the subtasks are always the same.

So I would like (I assume a formula?) the "sub task options" to appear when I select "project size" type.

IE: I select "XSMALL" in one cell and all the blank cells below show all the "XSMALL" subtasks to complete. (same for each size)

Hoping this can occur in another sheet

Hope this all makes sense

Mike TV

@Morris R

Let me know if this achieves what you're trying to do. Here's a sample database sheet I created first. You list out all of your possible Sub-Tasks for the different Project Sizes like so:

Then you create your lookup sheet. If Large is your biggest Project Size and it has 10 Sub-Tasks, for example, then you'll need to pre-fill a formula into 10 spaces where the Sub-Tasks will automatically be listed. I set up my working sheet like so:

Each of the 10 formulas which gathers the Sub-Task names are pre-filled into the sheet with a formula such as:

=IFERROR(INDEX(COLLECT({Project Size Database Sub Task Name}, {Project Size Database Project Size}, [Project Size]1), 1), "")

You only need to change the number near the end of the formula right before the double quotes ("") near the end. For the first row it'll be a 1, the next row it'll be a 2, and so on. You don't have to create multiple instances of this like the 2 I've shown above. You can just create the one set if you want. Where you see the Project Size on the 2nd sheet that gathers the Sub-Task names, you can make that a drop-down box if you like with the different Project Sizes to select from. When you change that cell to a different Project Size it will re-fill the Sub-Tasks with the appropriate ones for that Project Size.