Managers Reports List

[MarkyMark58]

I have a list of people in column A and a list of managers in column B of a table in an excel spreadsheet. Each month I run a separate series of reports, from a database,which only reports people and not their managers.

Is there a way to formulate a column in my monthly reports which identifies - from the 1st spreadsheet (the one which list who mangers who) and saves me having to type the manager's name next to their reports in the monthly reports?

[#2]

@MarkyMark58 

Let's say the sheet with the list is named Manager List.

On other sheets: let's say you have names in A2 and down.

In B2, enter the formula

=XLOOKUP(A2, 'Manager List'!$A:$A, 'Manager List'!$B:$B, "")

or if you don't have Microsoft 365 or Office 2021:

=IFERROR(VLOOKUP(A2, 'Manager List'!$A:$B, 2, FALSE), "")

Then fill down.

[#3]

@MarkyMark58 

#1: Are all of the managers listed in column B of the spreadsheet?  (To make discussion easier, please identify it by either its name or a description.)

#2: What do you mean by "formulate a column in my monthly reports"?  Are these Excel reports?  Are you willing to allocate a column in each (or in a separate worksheet) for the unique manager names (and possibly related data)?  Is there more than one "monthly report" per month (you used the plural "reports")?

#3: What do you mean by "type the manager's name next to their reports"?  Is each report in its own worksheet?  Or does one report worksheet have all the managers, and multiple sections?  Or...?  Posting/providing a small example (with proprietary information and PII replaced by some dummy values) would be useful.

#4: Please identify which version of Excel you are using.

Yes, you can populate a column of unique manager names in, say, worksheet Report1 from column B of [whatever that detail worksheet is].  An easy way might involve using the FILTER function in a formula, or manually using the menu items described in this Microsoft documentation.  It might also involve a VLOOKUP or similar function, as Hans notes.

[#4]

@Hans Vogelaar 

Thanks! I'll give this a try.

[#5]

#1 Name of Excel file is Managers List. Column A lists full names of people (eg Mark Smith) and Column B lists their manager (eg Paul Edwards)
#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

[#6]

@MarkyMark58 

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)