Oct 11 2022 08:08 AM
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?
Oct 11 2022 08:36 AM
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.
Oct 11 2022 08:54 AM
#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.
Oct 11 2022 09:34 AM
Oct 11 2022 10:21 AM - edited Oct 11 2022 10:41 AM
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?
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)