If this then that....

[charliebeth]

Hi, hope someone can help us with this.

I have two spreadsheets with data and I want to pull info from one to the other. Basically:

If this persons employee number shows on sheet 1, check sheet 2 for the same employee number, and display info from the next cell on sheet 2 on sheet 1.

Hope that's clearer than it reads back.....!

[63falcondude]

Take a look at VLOOKUP or INDEX MATCH.

If you require more specific assistance, consider creating a small representative sample workbook along with the desired results based on that sample data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[charliebeth]

I want to make this a bit clearer because it reads back like mud! It's for absence reporting.

I have a sheet with downloaded info including employee names and employee numbers, listing all the people who were absent over the space of a week (how many days absent etc).

My second sheet also records the names and employee numbers of people who are absent, but includes the reason why they are absent (this is completed manually after checking the absence line).

I want to be able to get the first sheet to search the second sheet for the employee number, and display the reason for absence - pulled from the second sheet and displayed on the first sheet.

[charliebeth]

Take a look at VLOOKUP or INDEX MATCH.

If you require more specific assistance, consider creating a small representative sample workbook along with the desired results based on that sample data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

I'll give that a go

[ANGIE90]

Hello,

I'm not an excel expert, but here's a solution. Not sure if it's the easiest way.

I converted the data on sheet1 to Table1 and the data on sheet2 to Table2. Then I added formulas. Please see attached.

Hope it helps

[AliGW]

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

[ANGIE90]

That's very good to know. Thank you.

[AliGW]

Angie90 - please add the detail I requested to your post. Your response suggests you don't understand the request - if that is the case, let me know Thank you.

[ANGIE90]

I used the INDEX/MATCH function to return the values and used IFERROR to cover up the #N/A error messages in the sample workbook.

Sorry for some reason the system won't allow me to post the formula.

[AliGW]

I’ll do it for you:

=IFERROR(INDEX(Table2[#All],MATCH([@[Employee Name]],Table2[[#All],[Employee Name]],0),),"")

[ANGIE90]

Thank you, Ali!

[AliGW]

You’re welcome.

[charliebeth]

Hi

Thanks for your replies so far. I am still struggling with this, so I'll try to upload my test sheet.

Using the Emp No to find the right person, I need Shift, Line and Reason to be populated on the first sheet from the second sheet.

[kersplash]

In E2 try this;

Please Login or Register  to view this content.

[charliebeth]

In E2 try this;

Please Login or Register  to view this content.

AWESOME! Thank you, that works

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[JohnTopley]

Try

in E2

=VLOOKUP($C2,A.D.D.!$B$2:$M$8,9,0)

OR if column heading are consistent ..

=INDEX(A.D.D.!$B$2:$M$8,MATCH('Absence Table'!$C2,A.D.D.!$B$2:$B$8,0),MATCH('Absence Table'!E$1,A.D.D.!$B$1:$M$1,0))

Highlighted in yellow in attached

[charliebeth]

Ah... I have hit a new problem with this.

That =INDEX(A.D.D.!J:J,MATCH('Absence Table'!C2,A.D.D.!B:B,0)) works great, until it's trying to use info that has been displayed in a pivot table. Any suggestions?

Referring to the example I shared above, when the first sheet is a pivot table all the responses become N/A.

[kersplash]

If it's now in a pivot table then that changes the original question.

[charliebeth]

Yeah... Sorry. Only just noticed. I'll start a new thread!