Using HLOOKUP & VLOOKUP to pick data from another Worksheet


  1. Posts : 15
    Windows 7 Ultimate x64 Version 6.1 (Build 7600)
       #1

    Using HLOOKUP & VLOOKUP to pick data from another Worksheet


    Hi all

    I am creating a table at work, which lists the sales managers for different products and then further sub-sorted for different locations.

    The old table had every name manually filled into it. Now, I have to update the table as new people have come in and existing ones have left etc. I was wondering whether instead of manually updating each and every name, if I could create a new table in a new worksheet which holds the names and e-mails of the personnel and the main table can refer the names from this table? So after doing this, in the future if any changes are to be made again, I only have to update the table in sheet 2 instead of updating the entire table in sheet 1.

    Here is the outline of the tables in Question:

    Here is Sheet 1 which contains the name & e-mails of personnel based upon location and designation. I want to lookup the data for name and e-mail from the table in Sheet 2

    Using HLOOKUP & VLOOKUP to pick data from another Worksheet-sheet1.jpg

    Here is Sheet 2 which contains the name and e-mails of the personnel.

    Using HLOOKUP & VLOOKUP to pick data from another Worksheet-sheet2.jpg

    So can this be done? Any help would be greatly appreciated! :)
      My Computer


  2. Posts : 17,545
    Windows 10 Pro x64 EN-GB
       #2

    It's quite simple, indeed.

    Formula =SourceSheetname!SourceCell on a cell on your target sheet gets the value from source sheet. For instance in your example case Sheet 1 Cell D2 needs to have a formula =Sheet2!$C$3 (do not forget the exclamation mark after sheet name & dollar signs before row and column!). Now whenever the Manager North is changed (Sheet2 C3), the value is automatically updated in Sheet 1 Cell D2.

    This works for strings and formulas / sums as a source. If the source is a formula or a sum instead of a string, you can also right click source cell on sheet 2 and select Copy, then right click target cell on sheet 1 and select Paste Special > Paste Link.

    Kari
    Last edited by Kari; 31 Oct 2011 at 09:29. Reason: Typo
      My Computer


  3. Posts : 15
    Windows 7 Ultimate x64 Version 6.1 (Build 7600)
    Thread Starter
       #3

    Exactly what I was looking for! Thank you Kari! A simple and elegant solution :)
      My Computer


  4. Posts : 17,545
    Windows 10 Pro x64 EN-GB
       #4

    You are welcome :)

    Kari
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 23:46.
Find Us