Data Validation and formula to autopopulate several rows and columns

In the attached file I have 3 strength training programs on the programs tab. I would like to find a formula that would have these programs autopopulate the cells on the tab labeled workout sheet when the training program is selected from the drop down box on the workout sheet. The area that would need to populate from each program is the following TBL_3x10 B5:M8, TBL_3x5 B12:M17, TBL_3x3 B21:M27.

On the workout sheet tab I already have formulas in the following columns to calculate training weights once the programs are put into the cells. Week 1 E7:E17, Week 2 H7:H17, Week 3 K7:K17, Week 4 N7:N17.

Enter this in C7, then copy to the rest of the table.

=IFERROR(INDEX((TBL_3x10,TBL_3x5,TBL_3x3),ROWS($C$7:C7),COLUMNS($C$7:C7),MATCH($B$7,Programs!$A$3:$A$5,0))&"","")

Does that do what you need?

Yes it does work. Thanks for the help.

I have a lot more training programs that I would utilize. What would I need to do so that the part of the index formula where the tables are entered becomes more dynamic with each new training program entered on the programs tab? If I got up to 30-40 different programs that formula bar would be pretty messy.

Try this one, which needs to be array confirmed.

=IFERROR(INDEX((INDIRECT(Programs!$A$3:$A$5)),ROWS($C$7:C7),COLUMNS($C$7:C7),MATCH($B$7,Programs!$A$3:$A$5,0))&"","")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

I got the array to confirm but that formula only worked with TBL_3x10. The rest of the tables showed errors.

Sorry, I should have tested before suggesting. It appears that it only works with the long method.

An alternative would be to select the whole table From C7 to N17 in the workout sheet, press F2, then paste this formula

=IFERROR(INDIRECT($B$7)&"","")

And press Shift Ctrl Enter to commit the array to the entire table. It shows #N/A in the empty rows, which for some reason cannot be prevented by the use of error handling functions.

A simple but crude fix would be to make all of the tables in the programs sheet the same size as the table in the workout sheet.

Let me know if that works for you, I'll keep looking in the meantime to see if i can find a better method.

That does work for having the various training programs pop up from the drop down list. Now with the array formula over the entire table on the workout sheet I've lost the ability to have the training weights automatically calculate.

I've also created a 4th sample training program to test out and that program does not display automatically.

I've attached the updated file.

Thanks for all of the the help on this.

Oops, that was my bad, I didn't notice the formula in the weight column.

This is a bit slower to calculate, but it works correctly.