Solved Using HLOOKUP & VLOOKUP to pick data from another Worksheet

[Games Goblin]

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



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



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

 

[Kari]

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

 

[Games Goblin]

Exactly what I was looking for! Thank you Kari! A simple and elegant solution

 

[Kari]

You are welcome

Kari