CODEWITHSUNDEEP
excelspreadsheet
user2031026
user2031026

Reputation: 11

How do I use data from one spreadsheet to fill in similar fields in another spreadsheet?

Every month I have to generate a roster of several hundred employees, which includes contact information, duty station, rank, etc. My task is to fill in any blank cells with the relevant details, which I can either find from last month's spreadsheet or the internet database. The rosters don't vary much from month to month. How can I populate the new roster with information from the old roster? For example, if I have A1: John Smith, B1: (123)-456-789 in last month's roster and A1: John Smith, B1: n/a in this month's roster, is there a way to automatically find John Smith's number in last month's spreadsheet and have it appear in this month's spreadsheet?

Upvotes: 1

Views: 13094

Answers (1)

Jerry
Jerry

Reputation: 71538

You could use the formula for vlookup.

In cell B1 in the new sheet, you do:

=vlookup(A1, PreviousmonthSheet!A:B, 2, false)

vlookup basically finds the corresponding value in the sheet you select (in this case, the previous month sheet) and returns the value next to some index (in this case, John Smith).

In this case, it takes 'John Smith', looks it up in the sheet PreviousmonthSheet in column A, and returns the value that is in column B (second column from column A, hence the 2 there). False means exact match, you would most likely want to use this.

The formula is:

=vlookup(lookup_value, tablearray, column_index_no, [range_lookup])

EDIT: In case it's another workbook, you will need to insert the workbook name in the tablearray part like this:

=vlookup(A1, [Workbookname.xlsx]PreviousmonthSheet!A:B, 2, false)

Upvotes: 1

Related Questions