+ Reply to Thread
Results 1 to 6 of 6

Autofilling cells based on drop downs

  1. #1
    Registered User
    Join Date
    12-16-2014
    Location
    Somerset, England
    MS-Off Ver
    2010
    Posts
    49

    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
    Attached Files Attached Files
    Last edited by Suede2; 04-17-2019 at 05:49 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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. #3
    Registered User
    Join Date
    12-16-2014
    Location
    Somerset, England
    MS-Off Ver
    2010
    Posts
    49

    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. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    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. #5
    Registered User
    Join Date
    12-16-2014
    Location
    Somerset, England
    MS-Off Ver
    2010
    Posts
    49

    Re: Autofilling cells based on drop downs

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

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Autofilling cells based on drop downs

    Glad it worked for you - thanks for the rep.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. take action based on multiple drop downs in 1 cell
    By bboyd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-13-2018, 03:58 PM
  2. Change the drop down values, based on selection in other columns(drop downs)
    By zeebee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2017, 01:16 PM
  3. Populate cells based on drop downs and quantity
    By avozella in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-23-2015, 10:06 PM
  4. Custom drop downs based on other dropdowns
    By ravenmaniac in forum Excel General
    Replies: 1
    Last Post: 02-10-2015, 08:06 PM
  5. Create Drop Downs based on Conditions
    By cmdeceia in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-17-2013, 03:00 PM
  6. Replies: 17
    Last Post: 02-05-2013, 08:20 PM
  7. Auto-update initial drop-down selection based on previous drop-downs
    By thornomad in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2010, 09:55 AM

Bookmarks

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