|31 Oct 2011||#1|
| || |
Using HLOOKUP & VLOOKUP to pick data from another Worksheet
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!
|My System Specs|
|31 Oct 2011||#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.
|My System Specs|
|Similar help and support threads for2: Using HLOOKUP & VLOOKUP to pick data from another Worksheet|
|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 |
© Designer Media Ltd
All times are GMT -5. The time now is 03:28 PM.