Forum Discussion
XLook Up
I have 3 workbooks. One called PO number system and one for each project manager.
Workbook 1 - PO Number system
Workbook 2 - Marc
Workbook 3 - Kyle
On the PO Number System, Column A is dropdown for project manager name.
Column B is PO number (generated from formula tied to Customer List in same workbook)
I want the project manager to be able to fill in the remainder of the columns (C-K) and it automatically fill in on their corresponding workbook - if Column A2 on PO Number System Workbook says Marc, then I want Marc's workbook to automatically fill in everything associated with that line 2 from the PO Number System Column. If it doesn't say Marc's name, then nothing happens to his spreadsheet. If A2 says Kyle, then everything should fill in on Kyle's workbook.
Spreadsheets attached for reference.
18 Replies
The PO Number System workbook does not contain the contract date.
Open all three workbooks.
In B2 in the Marc workbook:
=CHOOSECOLS(FILTER('[PO Number System.xlsx]PO'!$B$2:$K$50, '[PO Number System.xlsx]PO'!$A$2:$A$50="Marc", ""), 2, 3, 1, 7, 8, 9, 10, 4, 5, 6)
In B2 in the Kyle workbook:
=CHOOSECOLS(FILTER('[PO Number System.xlsx]PO'!$B$2:$K$50, '[PO Number System.xlsx]PO'!$A$2:$A$50="Kyle", ""), 2, 3, 1, 7, 8, 9, 10, 4, 5, 6)
Close the PO Number System workbook.
Excel will add the path of this workbook to the formulas.
Then save the Marc and Kyle workbooks.
- When I insert the formula into B2 I get an error - this function isn't valid. It is highlighting the word filter in the formula??
The FILTER function is available in Excel in Microsoft 365 and Office 2021.
Which version do you have?
