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
Games Goblin

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.

-sheet2.jpg

So can this be done? Any help would be greatly appreciated!




My System SpecsSystem Spec
.
31 Oct 2011   #2
Kari

Microsoft Community Contributor Award Recipient

 

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
Games Goblin

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
Kari

Microsoft Community Contributor Award Recipient

 

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
Thread Forum
Help!! Excel worksheet look empty after Office 2010 uninstall
Hello Big problem for me I install Office 2013 about 2 months ago. Prior, I was using Office 2010 after Office 2007 etc. Today, I saw the both version was install on My PC. Office 2013 and Office 2010. So I close all Excel sheet and proced to uninstall Office 2010 and Office 2010...
Microsoft Office
Which P/S would you pick?
I have an Asus P8Z77-V Deluxe w/an Intel 3770k that I'm building. Coolermaster StormStryker and a Corsair H100i Trying to decide which of 2 onhand P/S to use. Silverstone Strider Gold S-850w or a Corsair RM850? -D-
Hardware & Devices
Which tv should i pick?
Samsung - 46" Class / 1080p / 60Hz / LCD HDTV - LN46D550K1 Samsung - 43" Class / 720p / 600Hz / 3D Plasma HDTV - PN43D490 Both are 3d but the first is 1080p and the second is 720p. Some people say they can see a difference and some cant. Im leaning toward the first since its full HD but it...
Chillout Room
W7U Pick Me up!
Been running W7U about a year now,, it performs well,, I like to tweak things and i have my system running quite well i think but it still seems to just lack that wow factor. I dont have my system overclocked nor do i have my Geforce overclocked but i know iwth my set up i could probably easily...
Performance & Maintenance
Delete empty cells in worksheet
I am a newbie to Excel and have created a small calculation worksheet. It only occupies a small section of the worksheet and I would like to know how to only show that part and not showing the empty cells. I am using Microsoft Office 2007. Thanks jack
Microsoft Office
can someone help me pick the right one please
well guys i have win7 ult now but it's not any use to me, so i was thinking about downgradeing to pro or home prem but don't no what, one to get as it is an everyday pc with bits of office work done on it by my mum, but i use it for internet and other things plus streming, videos to my ps3 but i...
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 23:11.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App