Inserting a group of information on another sheet based on a drop down value.

Hello all,

I believe I've attached the spreadsheet properly. If I haven't, please advise.

What I am attempting to do:
You'll see a series of times shown as breaks and lunches on the "0800 - 1630" tab.
What I would like to have happen is when I choose something on the drop down box located in H2 of "Sheet6", I would like the area from H8 & I8 to H32 & I32 to populate with the chosen values as shown in the other sheet. (i.e. if I chose 15 for H2, I would like the "15" columns of the 0800-1630 tab to show up below, which are columns K & L on tab 0800-1630.)

I need to repeat this a lot for various times, and invent other time schedules and what not, but I didn't want to do that until I knew how to import it and what not, so I just have this partial built to not steal focus. I thought of various ways to frame a VLookup, but I just couldn't get that to work.

If you need any further clarification, please ask and I'll try and respond as quickly as possible. Any help that could be offered would be most beneficial!

ScheduleHypo.xlsx

Created named ranges for each of the break/lunch blocks on 0800-1630 sheet. E. g. the name for the 10 block is "_10".
Created named range for row 1 of that sheet for use in dropdown in H2(I left the blank cells).

Used:

Please Login or Register  to view this content.

Used INDIRECT to prepend the "_" to the value in row 2 to create the named range from the dropdown value selected. 1 refers to column one of the named range; change to 2 for column I formula.

The IFERROR formula does not refer to the 0800-1630 tab directly. The INDIRECT function puts an underscore before the value chosen from the dropdown list, if you choose 13 the result is _13 which is the name of the range ='0800 - 1630'!$G$3:$H$15 which is used by the INDEX function.

If you add a new sheet, you will have to add 15 new named ranges referring to that sheet.

I don't know what you are actually doing but playing with Monday:
Replaced the times in column G with a new DV dropdown in G6. Moved the 10 - 25 dropdown to H6.

Deleted all the named ranges and replaced them with named ranges for the sheets corresponding to the time dropdown choices. I.e., tab "0800 - 1630" is referred to by name_0800_1630, etc.

Formula for G8:G32 --->

Please Login or Register  to view this content.

Formula for H8:H32 --->

Please Login or Register  to view this content.

Say you have in G8: 10:00 - 18:30.
The SUBSTITUTE function is used to replace the ":" with nothing and the " - " with "_" resulting in 1000_1830, then another "_" is added to the front: _1000_1830 this is the name that refers to sheet 1000 - 1830 which INDIRECT presents to INDEX as an array (the whole sheet)