+ Reply to Thread
Results 1 to 6 of 6

VBA code to compare names entered in multiple sheets - Work Schedule

  1. #1
    Registered User
    Join Date
    07-30-2020
    Location
    Loveland, Colorado
    MS-Off Ver
    Office 360 ProPlus
    Posts
    3

    VBA code to compare names entered in multiple sheets - Work Schedule

    I'm hoping someone on here can help we with what I'm trying to create in our Excel work schedule.

    In the Sheet "Availability" it lists all of the employee's in Column B. With the date listed in Row 2.

    In the Sheet "Assignments" is where we enter each employee's assignment for that day (Date is listed in Row 2).


    When you enter a name into a cell in "Assignments" I need it match the name to the Employee name in "Availability" to verify the spelling is correct (we have multiple Thompsons and other names, so they are listed "Thompson, A" and "Thompson, N") and it's matching the correct person in "Availability"

    Secondly I need it to verify that the employee is actually scheduled to be working (not on vacation or otherwise off). It would have one of the following "Days, E Swings, L Swings, Lates" in the date for the employee if they are scheduled to work. Anything else or blank would have them off. This is the "Availability" sheet.


    I'm hoping that either as you enter the information it pops up a message if there is something wrong (name spelled wrong, not scheduled to work) so it can be corrected, or be able to click a box that will verify either the shift or the whole day to make sure everything is correct.


    I've tried looking at doing =IF statements but that would be in each cell and once you add information it would be over written, plus that many formulas will overload the workbook even on opening.

    Any help would be great, I'm lost.

    availabilty.pngAssignments.png

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,847

    Re: VBA code to compare names entered in multiple sheets - Work Schedule

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new, I will do it for you today: https://www.mrexcel.com/board/thread...-list.1141522/.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,379

    Re: VBA code to compare names entered in multiple sheets - Work Schedule

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,847

    Re: VBA code to compare names entered in multiple sheets - Work Schedule

    Alan - the OP is new here, so I've provided the link for him this time.

  5. #5
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,205

    Re: VBA code to compare names entered in multiple sheets - Work Schedule

    a cut down copy of you workbook would be useful. help us to help you with a solution first time.
    see big yellow banner - how to upload a workbook.

  6. #6
    Registered User
    Join Date
    12-17-2016
    Location
    South Wales
    MS-Off Ver
    Excel 2007, 2016
    Posts
    64

    Re: VBA code to compare names entered in multiple sheets - Work Schedule

    (Moderators: thanks for flagging the cross posting. As I'd already started replying on one forum, I'm posting this reply to both - hope that is ok.)

    I've created a mini version to demonstrate - link here: https://drive.google.com/file/d/17hx...ew?usp=sharing Explanation and VBA are below

    I've create two named ranges:
    "Avail": Availability sheet, from B2 to the bottom right corner of data - i.e. including date headings/names
    "Assign": Assignments sheet, from B2 to the bottom right corner of data - i.e. including date headings, but excluding anything before the first column containing names.

    I've created user form PickName, with combo box AvailableNames and command button OkButton.

    In the VB Editor, this code goes in the bit for the assignments sheet:
    Please Login or Register  to view this content.
    This code goes in the bit for the user form:
    Please Login or Register  to view this content.
    Edit - hopefully the example file is also now attached. The Google drive link above is because the other forum doesn't allow file uploads.
    Attached Files Attached Files
    Last edited by Trevor_S; 07-31-2020 at 12:43 PM.

+ 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. Replies: 1
    Last Post: 08-25-2016, 11:19 AM
  2. Replies: 3
    Last Post: 04-06-2016, 10:59 AM
  3. Formula required to compare names on two different work sheets
    By Spaz1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2013, 05:59 PM
  4. Rotating Names in Work Schedule
    By DukeDiablo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2013, 05:57 AM
  5. VBA Code to work on multiple sheets in a workbook
    By Ironman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2012, 06:35 PM
  6. Can this code work with multiple sheets in one workbook
    By clbutler2008 in forum Excel General
    Replies: 1
    Last Post: 07-11-2008, 07:50 PM
  7. change code to work over multiple sheets
    By cda_cmd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-14-2006, 09:10 PM

Tags for this Thread

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