Excel Help Please

**leedav1978** · 12-08-2009

Hi

I need some advice on how I can get 2 x Excel workbooks sharing data and updating each other. The scenario I have is as follows.

WB1 contains customer names in column A, customer subsidary names in column B and names of individuals (account managers) in column C
WB2 contains quotation information for customers that may or may not appear in WB1, WB1 has a designated column for customer name (column D)

What I need to happen is when a customer name is submitted in WB2 column D, I need it to talk to WB1, search for a match (or similarity if possible) against the customer name in Column A and/or Subsidary in column B and once a match is found enter the account manager details (from WB1 column C) in one of the columns in WB2.

Help ! - Any questions please ask

Thanks
Lee

**farzim** · 16-09-2009

Hi,

You can do so by using vlookup.
In wb1 worksheet I ve used col A as customer name, col B As Customer subsidiary name,col C as account manager

In wb2 I ve used col A as customer name,col b as account manager
In col B Ive used the formula,=IF(ISERROR(VLOOKUP(A6,'[wb1.xlsx]Sheet1'!$A$2:$D$100,3,FALSE)),IF(ISERROR(VLOOKUP(A6,'[wb1.xlsx]Sheet1'!$B$2:$D$100,2,FALSE)),"Empty",VLOOKUP(A6,'[wb1.xlsx]Sheet1'!$B$2:$D$100,2,FALSE)),VLOOKUP(A6,'[wb1.xlsx]Sheet1'!$A$2:$D$100,3,FALSE)), which would select the account managers name from col C in worksheet WB1 if available.

Yo will have to change the table details "$A$2:$D$100" based on how u have entered the data
Good Luck !

**chroma** · 17-09-2009

Microsoft Excel shared workbooks provide a simple, flexible way for a group of people to collaborate on a list or other data-driven project. If you share the workbook, then each person opens the workbook itself and works in the workbook. It imposes some limitations on the workbook, but I don't think anything that would affect what you are doing. I would pursue that first you try to develop a custom solution. With a shared workbook, you can design your workflow around familiar Excel features.