what formula/function to use to link specific information between worksheets

[ckelly3493]

I am trying to create a workbook for employee information. One the first worksheet - Employee Master Data - I have all information i need for all employees (name, address, hire date, birthday, emergency contact info, etc.) Then I have another worksheet - Employee Birthday List - and I want to be able to have that worksheet automatically populate the employees names and birthdays, sorted by birthday month. I want the information to be pulled from the Employee Master Data worksheet, so that whenever a new employee is added to the Master Data, it gets added in the right spot on the Employee Birthday List, but I don't know what type of formula or function to use.

[TheCman81]

You can use VLOOKUP to achieve this

Can you post some sample data and I can show you

[sktneer]

Please find the attached to have an idea about how it can be achieved.

Does this help?

[ckelly3493]

Sample Employee Data File.xlsx
Okay here's the sample workbook...I tried using a VLOOKUP, but I couldn't get it to work in this situation, but maybe I was doing it wrong... Thanks!

[TheCman81]

Go with sktneer's formula, that should do the trick

[sktneer]

Please find the attached sheet with modified layout and formula.
The sheet contains two Names Ranges "EmpName" and "Birthday" which are used in the formulas. So when you enter a EmpName and its details in the master sheet, it will be automatically populated in the Birthday sheet.

Is this what you are trying to achieve?

[ckelly3493]

Yes, Thanks! Except, I'm not sure how to put that formula into my actual spreadsheet... what does an "array formula" mean?

[sktneer]

You're welcome.

To apply the formulas in your actual workbook, follow these steps......

Click on Formulas --> Name Manager --> Here you will find two Named Ranges "EmpName" and "Birthday". Now click on EmpName, in the refers to box below you will find an Offset formula which is referencing col. A because in the attached sheet the EmpNames are in col. A, so if in your actual workbook, EmpNames are in different column, just replace A with that column letter. Repeat the same with Birthday named range.
Now the formula on sheet Birthday will refer to the correct Named Ranges and will return the correct output.

As far as Array Formulas are concerned, these formulas requires special key storks i.e. Ctrl+Shift+Enter to confirm a formula instead of just Enter which you normally do in case of a normal or regular formula.
To have a better understanding about the array formulas, go through the following links.

http://office.microsoft.com/en-in/ex...001087290.aspx
http://www.excel-easy.com/functions/array-formulas.html

If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.