+ Reply to Thread
Results 1 to 12 of 12

Find Dates and Names from one sheet and match with another

  1. #1
    Registered User
    Join Date
    03-29-2020
    Location
    UK
    MS-Off Ver
    365
    Posts
    8

    Find Dates and Names from one sheet and match with another

    Hi, this is my first visit here, having used the site several times to help me get as far as I have so thanks everyone.

    Due to having to work from home and having to justify my existence, I have had to try and learn VBA, macros etc and "upgrade" some of our worksheets we use at work, as such I am using Excel 365 and unable to install any 3rd party add ons.

    I am currently working on upgrading the work leave planner. I have created a userform to capture all the required info - Name, Type of Leave and Start/End Dates and Comments required. Due to not having the date picker option, the dates are added manually (dd/mm/yyyy) in a textbox (one for start and one for end)

    I have managed to export all the data from this form back into a separate sheet so all the info is there. However, what I need it to do it look at the name and date and complete the associated cell on the main planner with the type of leave required.

    I have attached a sample workbook of a very basic version, the actual version has 50+ staff over different shift patterns with more leave types but the principles are the same.


    To summarise, I need the worksheet to look at the name, date and leave type in "Sheet2", find the same name and date in "Sheet1" and complete the related cell with the leave type. Sheet 2 will continuously be populated via the userform so there may well be more entries added for the same staff member further down the sheet, not all in one place.

    Hopefully that makes sense, any guidance would be much appreciated.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by ChipmanCharlie37; 04-07-2020 at 08:51 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Find Dates and Names from one sheet and match with another

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Find Dates and Names from one sheet and match with another

    Hi Charlie, we really require a sample workbook on this site. We are donating enough of our time without having to retype pictures into a book and then find out we did something different to your book. I'm not being nasty, sorry if it sounds that way, it is just way easier for us to work with and we're usually too lazy to retype peoples pictures.
    Saying that, in Sheet2 try using index/match (plenty of examples here) to find your info.
    Index works on a grid reference. Index(Array, Row, Column). So in your sample - Array($A$2:$D$5,4,4) would return 30/08/2020. That is the 4th row down and 4th row across in your "grid".
    Using Match for the name would replace the first 4 (row). Matching Sheet2's Alf against Sheet1's Column A will give you the Row number. Then just select 3 for the start date column or 4 for the end date column.
    Using that lookup system you can tell excel to place the data from the lookup column 2 (AL in Alf's case) into the cell IF the date above >= column 3 AND <= column 4.
    Hope that makes sense

    Edit:- Sorry Pepe Le Mokko, I should have refreshed before posting.

  4. #4
    Registered User
    Join Date
    03-29-2020
    Location
    UK
    MS-Off Ver
    365
    Posts
    8

    Re: Find Dates and Names from one sheet and match with another

    Thanks, I have now added a sample workbook

  5. #5
    Registered User
    Join Date
    03-29-2020
    Location
    UK
    MS-Off Ver
    365
    Posts
    8

    Re: Find Dates and Names from one sheet and match with another

    Thank you very much Beamernsw, I've now added a sample workbook, apologies.

    I've been told it's time for my daily exercise so will try your answer out when I return but really appreciate your time.
    Last edited by AliGW; 04-07-2020 at 10:34 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Find Dates and Names from one sheet and match with another

    Hi Chipman and welcome to the forum,

    I tried to write my own calendar controls before the Date Picker without complete success, but found someone else had written one using standard VBA. See if this one could substitute. I believe one of our Guru's wrote it but didn't put his name in the code for us to thank.
    Any Cell Calendar Input.xlsm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Find Dates and Names from one sheet and match with another

    Administrative Note:

    Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
    Use the "Quick reply" instead
    Thanks

  8. #8
    Registered User
    Join Date
    03-29-2020
    Location
    UK
    MS-Off Ver
    365
    Posts
    8

    Re: Find Dates and Names from one sheet and match with another

    Hi MarvinP,

    Thank you, that could be very handy.

  9. #9
    Registered User
    Join Date
    03-29-2020
    Location
    UK
    MS-Off Ver
    365
    Posts
    8

    Re: Find Dates and Names from one sheet and match with another

    Thanks Beamernsw, I've tried your suggestion but have struggled to be honest, I think I need to start from scratch!

    I got the first two lines ok,

    =INDEX($A2:$D$7,MATCH("Alf",A2:A7,0),3)

    Which returns the start date or end date if I change it to 4.

    How do I then use that to populate the relevant date with the type (column 2) baring in mind there will be more entries added at a later date and not necessarily in order so for instance Alf could have another entry for HRA on different dates further down the "grid".
    Last edited by ChipmanCharlie37; 04-11-2020 at 05:57 AM.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Find Dates and Names from one sheet and match with another

    Tell them that you are learning about Excel tables and structured referenced formulas as in:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  11. #11
    Registered User
    Join Date
    03-29-2020
    Location
    UK
    MS-Off Ver
    365
    Posts
    8

    Re: Find Dates and Names from one sheet and match with another

    JeteMc,

    You are indeed a Guru - thank you so much. Works exactly as I needed it to and to be honest I was no where near getting that, I was going completely down the wrong route so I am very grateful.

    Thanks everyone for your support.

    Dan

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Find Dates and Names from one sheet and match with another

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I left a few things unmentioned so that you can truthfully tell someone that you are learning about things like how to expand a table and how the [[Type of Leave]:[Type of Leave]] syntax of a structured reference is similar to absolute references normal reference formulas (i.e. $A$1).
    Let us know if you have any questions.
    I hope that you have a blessed day.

+ 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. Using Index/Match/Match, calling different sheet names
    By Dori_Vetaroo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-20-2020, 03:54 AM
  2. Index and Match help with names and calendar dates
    By janger in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-19-2019, 09:01 PM
  3. Find and match names on a different sheet and then copy the adjacent cells
    By windrain in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-01-2018, 06:31 PM
  4. Index Match to find match date to a range of dates
    By downcrusher in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2017, 06:05 PM
  5. [SOLVED] Match names and dates from source data
    By razaas in forum Excel General
    Replies: 3
    Last Post: 11-07-2013, 09:10 AM
  6. [SOLVED] Match dates and names
    By bigband1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-06-2013, 10:36 AM
  7. Match dates and names on different sheets and colour cells
    By ExCelNoob_ in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-24-2010, 03:56 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