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.