# Autofilling cells based on drop downs

1. ## Autofilling cells based on drop downs

Good Afternoon,

I am after some help creating a Rota generator for my office. Most of the time the staff work to set shift patterns, but because I've been instructed to provide a visual view to make sure all there areas are covered I have to keep the view that has been given to me.

What I am after is in the shift column there is a drop down allowing someone to select the shift required. Once the shift is selected, I want the cells to the right (Time Fields) to get filled with something for the Conditional Formatting to colour it in. There are some extra calculations that will be made in the form that will use the information populated which is why I need to use this format.

In the example is a sheet called 'Rota' which is blank, and another called 'What I would like' as an example.

If any help could be provided that would be great. I have had a lot of success in the past here when I cant work something out.

Thanks
Sam

2. ## Re: Autofilling cells based on drop downs

You can put this formula in D3:

=IF(\$C3="","",IF(AND(--(LEFT(\$C3,5)&":00")<=D\$1,--(RIGHT(\$C3,5)&":00")>=D\$1+TIME(0,15,0)),1,""))

then copy across and down, to fill the cells with the value 1 where the shift occurs, then your CF condition should turn this to black.

Hope this helps.

Pete

3. ## Re: Autofilling cells based on drop downs

Hi Pete_UK

This is 99% perfect. For the shifts that end at 17:00 though it stops a cell early. Is that easily remedied?

4. ## Re: Autofilling cells based on drop downs

Put this formula in E1:

=D1+TIME(0,15,0)

then copy across to AO1.

Hope this helps.

Pete

5. ## Re: Autofilling cells based on drop downs

That's perfect. Thank you very much. Will mark it as solved.

6. ## Re: Autofilling cells based on drop downs

Glad it worked for you - thanks for the rep.

Pete

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1