Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Using HLOOKUP & VLOOKUP to pick data from another Worksheet


31 Oct 2011   #1

Windows 7 Ultimate x64 Version 6.1 (Build 7600)
 
 
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

Name:  Sheet1.jpg
Views: 14
Size:  73.5 KB

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 System SpecsSystem Spec
.

31 Oct 2011   #2

Microsoft Community Contributor Award Recipient

Windows 8.1 Pro with Media Center
 
 

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
My System SpecsSystem Spec
31 Oct 2011   #3

Windows 7 Ultimate x64 Version 6.1 (Build 7600)
 
 

Exactly what I was looking for! Thank you Kari! A simple and elegant solution
My System SpecsSystem Spec
.


31 Oct 2011   #4

Microsoft Community Contributor Award Recipient

Windows 8.1 Pro with Media Center
 
 

You are welcome

Kari
My System SpecsSystem Spec
Reply

 Using HLOOKUP & VLOOKUP to pick data from another Worksheet




Thread Tools



Similar help and support threads for2: Using HLOOKUP & VLOOKUP to pick data from another Worksheet
Thread Forum
Help me pick a Motherboard and CPU Hardware & Devices
Help Me Pick a Mouse Hardware & Devices
Which tv should i pick? Chillout Room
How do I pick the right Executor for my will ? Chillout Room
W7U Pick Me up! Performance & Maintenance
Delete empty cells in worksheet Microsoft Office
can someone help me pick the right one please General Discussion

Our Sites

Site Links

About Us

Find 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 08:28 AM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33