Forum Discussion
Managers Reports List
#2 I export data from Salesforce database to an Excel sheet each month. This generates reports which include in one of the columns this list of workers (eg Mark Smith). I want to insert a new column and label it "Manager" and write a formula which picks the manager from the spreadsheet Mangers List and imports the name of the manager (eg Paul Edwards) next to the relevant colleague (eg Mark Smith)
#4 I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20704) 64-bit
I intended to ask for the name of the worksheet, not the name of the workbook (spreadsheet), but your response makes the situation clearer.
Yes, you can use a formula similar to the following if the Managers List worksheet (its name?) column A data is sorted:
=VLOOKUP( <empee_name_cell_in_report_worksheet>, '[Managers List.xlsx]<worksheet_name>!$A$3:$B$995, 2 )
(assumes that employees are sorted by full name, starting in row 3 through row 995)
<empee_name_cell_in_report_worksheet> needs to be a relative reference, such as D5 (not absolute, such as D$5). The manager name should appear. Copy that formula down the column.
Strongly recommended: Then copy that entire range in the new column onto itself, replacing the formula with its values. Why?
- The person viewing the report might not have access to the Managers List file.
- The manager for a given person may change over time; you presumably want the name of the manager ~when the report was generated.
If the data in <worksheet_name> is not sorted (or cannot easily be maintained that way), look into using XLOOKUP instead of VLOOKUP.
Edit - corrected the first replacement tag (for the employee full name in the report worksheet)