+ Reply to Thread
Results 1 to 6 of 6

Vlookup / Hlookup / Lookup which copies info from an external source! Help Please!

  1. #1
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Exclamation Vlookup / Hlookup / Lookup which copies info from an external source! Help Please!

    Hey Excel Forum!

    First Post so be nice!

    I have 2 files, one has a ROTA with names down left, dates along top, which will be changed every week depending on people shifts.

    I have a second workbook with TELEPHONY statistics for each member of staff on a given day.

    Using Data Validation, I can select which claim handler i want to add information for from the list.

    Then input the date i want the info for next to the name in format 23/07/2012

    I then want a formula / macro (preferably formula!) to look at that persons name, search the ROTA file for their NAME down the left, AND look for the DATE along the top, and then pull the information for which shift they are doing into the TELEPHONY worbook.

    e.g. if the date is in column G, and their name is in row 11, their shift would be locate din cell G11. I want the lookup(s) to give me this by searching the persons name and date, so that if these were changed to another person or date, would still find the information I want?

    Any Help Please Guys!

    Thanks in Advance!

    Chris

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Vlookup / Hlookup / Lookup which copies info from an external source! Help Please!

    You'd probably use INDEX/MATCH(row)/MATCH(column) to do that.

    Please post a sample workbook if you want more specific advice.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Re: Vlookup / Hlookup / Lookup which copies info from an external source! Help Please!

    Hi TMS,

    I've attached the ROTA file and the TELEPHONY Workbooks.

    As you will see, the ROTA, file is done as a series of 5 colums (A, B, C, D, E) A "1" appears in the column for which shift that person is doing. Then in cells AK2:AN9 there is a table showing what start & finish time that person does.

    I want to be able to manually input the date in format 23/07/2012 in the TELEPHONY spreadsheet, then select the handlers name from the drop down, then have it automatically pull in their shift for that day. I assume will have to insert an IF statement somewhere to identify what shift they are working. My main problem at the moment is coming up with the formula that searches name down left, date along top, to narrow down which 5 smaller cells for the IF statement to look at.

    If it helps, I have named the range with peoples names on the rota as "rotaname" and the cells with dates in as "rotadate", and on the telephony sheet, the column where the date goes is named "date" and where the handlers name goes from the drop down is named "name1".

    Any more q's let me know! Thanks for your help TMS!

    Chris
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Re: Vlookup / Hlookup / Lookup which copies info from an external source! Help Please!

    Hi TMS, just noticed the data validation isn't working to select the handlers name.

    I think this could be because I set it up in 2010 at work and only have 2007 at home!

    Chris

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Vlookup / Hlookup / Lookup which copies info from an external source! Help Please!

    is the "Telephony" workbook the "dawn's call Spreadsheert"? if not, then perhaps you uloaded the wrong file?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Re: Vlookup / Hlookup / Lookup which copies info from an external source! Help Please!

    Yeah it is sorry. The Dawn's Call Spreadsheet is the telephony one.

    Sorry!

    Chris

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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