Question description:
This user has given permission to use the problem statement for this
blog.
Hello, I need to figure out how to link a cell in one sheet to a cell in another sheet, but those cells need to link if and only if they have the same part number in a different cell how do I do this?
Solved by A. U. in 21 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
21/05/2018 - 03:08
Hello, I understand that you would like to link data between 2 sheets but only if the part numbers are the same?
User
21/05/2018 - 03:09
Correct. I do not know how to link two cells that depend upon the number in another cell
Excelchat Expert
21/05/2018 - 03:09
I see. I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
User
21/05/2018 - 03:09
Okay
Excelchat Expert
21/05/2018 - 03:09
For me to have a better understanding of the requirement, are you able to share your file so I can analyze it?
User
21/05/2018 - 03:10
I do not know if it is sensitive information or not so I am not able to share it. I can mock it up in the example sheet on the right
Excelchat Expert
21/05/2018 - 03:11
That'll be great. Please go ahead and create a sample sheet. It would help if the columns and sheet names will be accurate as well.
User
21/05/2018 - 03:13
Okay so I want to pull info from sheet two, but the problem in the cells will not always be in the same order so I need them to connect based on their part number
Excelchat Expert
21/05/2018 - 03:14
Alright, that makes sense.
Excelchat Expert
21/05/2018 - 03:14
For this we can use the vlookup formula.
Excelchat Expert
21/05/2018 - 03:14
It's the easiest approach.
Excelchat Expert
21/05/2018 - 03:14
One moment.
Excelchat Expert
21/05/2018 - 03:14
Is inventory and on-hand the same?
User
21/05/2018 - 03:15
what dpo you mean
Excelchat Expert
21/05/2018 - 03:15
In sheet1 column B is called On-Hand.
Excelchat Expert
21/05/2018 - 03:15
In sheet2, it's inventory.
Excelchat Expert
21/05/2018 - 03:16
Am I right to assume that you'd want to pull the Inventory from Sheet 2 and place it under On-Hand in sheet 1?
User
21/05/2018 - 03:16
yes
User
21/05/2018 - 03:16
sorry I mocked it up exactly how it has been sent to me
Excelchat Expert
21/05/2018 - 03:17
If that's the case then the formula in Sheet 1 has been created and it will return the data based on the part Number.
Excelchat Expert
21/05/2018 - 03:17
As you can see, 222222 and 111111 are not in the right order but the formula doesn't really care.
Excelchat Expert
21/05/2018 - 03:18
OLA and MPLT should be working as well now.
User
21/05/2018 - 03:18
Thank you how do I do this myself
Excelchat Expert
21/05/2018 - 03:19
If you have the exact same sheet names and columns then you can simply use the formula in B2 and paste it in your sheet1 B2.
Excelchat Expert
21/05/2018 - 03:19
So you can paste this:
Excelchat Expert
21/05/2018 - 03:19
=vlookup($A2,Sheet2!$A:$D,2,0)
Excelchat Expert
21/05/2018 - 03:19
in Sheet 1 B2 of your actual file.
Excelchat Expert
21/05/2018 - 03:19
That's of course, assuming that your sheets are named Sheet1 and Sheet2 and the Part numbers are in column A.
Excelchat Expert
21/05/2018 - 03:19
This is the reason why I asked you to be accurate when writing your sample data.
Excelchat Expert
21/05/2018 - 03:20
Now if they aren't exactly the same then the formula will have to be modified and you are the only one who will know what to modify it to since you are the only one who knows your actual data.
User
21/05/2018 - 03:21
Thank you
Excelchat Expert
21/05/2018 - 03:21
Did it work?
Excelchat Expert
21/05/2018 - 03:22
Were you able to make B2 work?
User
21/05/2018 - 03:23
I have not been able to so far, but my sheet looks incredibly different so I'm trying to sort through what I need'
Excelchat Expert
21/05/2018 - 03:23
Do you know how vlookup works though? I can try to explain how it works so you'll have a better chance of adopting it.
User
21/05/2018 - 03:24
I've used Vlookup a few times yes
Excelchat Expert
21/05/2018 - 03:24
Okay, let me just give you a quick rundown then.
Excelchat Expert
21/05/2018 - 03:24
Here's the formula for B2.
Excelchat Expert
21/05/2018 - 03:24
=vlookup($A2,Sheet2!$A:$D,2,0)
Excelchat Expert
21/05/2018 - 03:25
Basically, this looks at the value of A2 and compares id to the value in column A of Sheet2
Excelchat Expert
21/05/2018 - 03:25
If it finds an equivalent, it'll return the value in the second column of A:D.
Excelchat Expert
21/05/2018 - 03:26
Our session is about to expire in 1 minute and I can't do anything about that.
Excelchat Expert
21/05/2018 - 03:26
Are you still with me?
User
21/05/2018 - 03:26
I am I inserted part of my sheet to try and show you what I'm looking at
Excelchat Expert
21/05/2018 - 03:27
I can see that you've pasted your actual sheet but without the time, I won't be able to complete it.
Excelchat Expert
21/05/2018 - 03:27
I would have to suggest that you post your question again once this session expires and this time, give your actual data.
Excelchat Expert
21/05/2018 - 03:27
I'd appreciate a 5-star rating and your feedback if you think I deserve it.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
