Inserting names from another workbook

[Ginette Lacombe]

Good morning,

I have a master workbook that contains all data related to an employee. We also have 12 sectors within our organization and what I would like is a formula that will insert all the names from one of the 12 sectors into another workbook which contains certain columns and formulas.

Thanks

[Special-K]

Sounds like you need VLOOKUP or INDEX(MATCH())) but without any examples or specifics of what cells contains the data all we can provide is a general answer.

Attach a sample spreadsheet with expected results, remove any sensitive data. Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[loginjmor]

Hi -

It's hard to understand the problem from the information given. Can you simply have both workbooks open and then simply reference the cells with the names you are looking for in the master workbook from the other workbook?

[Ginette Lacombe]

I have uploaded the result spreadsheet as per your instruction, hope you see it and thank you for your time

[loginjmor]

OK - I'm looking at your file and I see a column for Name. Is there a specific search criteria? How do I know what name to put in each row of Column A?

[Ginette Lacombe]

Sorry, it needs to match what is in cell J1 called AEB, this is the sector name.

Thanks

[loginjmor]

OK - So each of the 12 names will have a cell next to it with "AEB" in it? And are all 12 names on one spreadsheet in this other workbook? Are the 12 names in subsequent rows going down? For Example:

Bob
Jane
Joe
Cindy

[Special-K]

Can we not have a copy of the other spreadsheet uploadeed, or even a mocked up version?
Then we wouldnt need to be asking what the other spreadsheet looks like.

The usual procedure would be to say:
I have uploaded this spreadsheet here
and uploaded this one here
These are the results I want
and this is how those results are obtained.

Then the questions would be kept to a minimum.

[Ginette Lacombe]

Thank you for the quick reply, unfortunately it contains too many sensitive information, it has 43 columns and 252 rows, but the Sector name is in column A and the employee name is in column B. I would like to insert the names from column B that matches the sector name in column A into the spreadsheet that I uploaded earlier.

Thanks again for all your help

[loginjmor]

OK -

Attached are two spreadsheets. You original that I have modified with an array formula to extract a unique list of names that reside in the second attached spreadsheet, based on 1 criteria. It looks like this:

=IFERROR(INDEX('I:\Jeff\XLF\[XLF Two Workbooks - Master Table.xlsx]Sheet1'!$B$2:B$10,MATCH(0,IF('I:\Jeff\XLF\[XLF Two Workbooks - Master Table.xlsx]Sheet1'!$A$2:$A$10=$J$1,COUNTIF($A$4:$A4,'I:\Jeff\XLF\[XLF Two Workbooks - Master Table.xlsx]Sheet1'!B$2:B$10),""),0)),"")

As I said, this is an array formula, so to engage the Array functionality, from the formula edit window, you have to push Ctrl-Shift-Enter at the same time. YOu will know if you did it right if Excel places curly braces {} around the formula.

Since I know nothing about your Master Workbook, you will have to do the work of making this formula idea work with your spreadsheets.

One final note, both the master spreadsheet and the results spreadsheet must be open for this to work. If the master spreadsheet isn't open, you will just get blanks.

Hope this helps!