Copying value of cell to another worksheet

June 13, 2011 at 09:04:00
Specs: Windows 7
Hello there,
I have tried hard to look for an answer somewhere but cannot find it. I was wondering if someone could please help me with this.

I need to do the folllowing:
Sheet 1 has Column A with Name and Column F with a Number.
Sheet 2 has Column A with Name and Column H.
I need to copy the value of Sheet 1 Column F to Sheet 2 Column H based on the name in Sheet 1 Column A

Thanks for your help!


See More: Copying value of cell to another worksheet

Report •


#1
June 13, 2011 at 10:15:36
Have you looked at the VLOOKUP function?

Let's say your Names and Values are in Sheet1!A2:F10

In Sheet2!H2 enter this formula and then drag it down.

=VLOOKUP(A2, Sheet1!$A$2:$F$10, 6, 0)

The assumption is that there is only occurrence of each name in the lists.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 13, 2011 at 11:30:00
Thank you for your post DerbyDad03.

When I put that formula in, it is prompting me to select a file.

Here is an example of what I am trying to do...

Sheet1
Col A Col B
Name Total
John 15000
Mary 23000
David 44000

Sheet2
Col A Col B
Name Total

The name column in Sheet1 is not identical to the name column in Sheet2.
I am doing a monthly sales report in Sheet1 and want it to update my Total column for the saleperson in Sheet2

Sheet 2 columns has Name then each month of the year

Thank you


Report •

#3
June 13, 2011 at 11:54:41
re: "When I put that formula in, it is prompting me to select a file."

Change the text Sheet1 in the formula to be the name of the sheet you are looking up the data from. i.e. the actual name of your Sheet1.

re: "The name column in Sheet1 is not identical to the name column in Sheet2."

What do you mean by not identical?

If the names are not in the same order, that's fine. If the columns don't contain the same names, then I wouldn't expect the formula to work since it's not going to find what it is looking for. This use of VLOOKUP requires matching names in each list to find what it is looking for.

re:

Sheet1
Col A     Col B
Name      Total
John      15000
Mary      23000
David     44000

Why are you telling me what's in Columns A & B when your OP asked about Columns A, F & H?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 13, 2011 at 12:09:35
sorry for the confusion

I am using the actual sheet name in the formula and yes I ment the 'Name' column is not in the same order.

I used the previous posting as an example that is why the Column Letters are different.

Can I ask what the 6 & 0 refer to?

Thanks :)


Report •

#5
June 13, 2011 at 12:29:52
re: "I used the previous posting as an example "

What previous posting? I see no post prior to yours where Column B is mentioned.

re: "Can I ask what the 6 & 0 refer to?"

Did you read up on the VLOOKUP function in the Excel Help files?

It will explain the arguments used by the VLOOKUP function. Based on the columns you asked about in your OP, it will all make sense after you read the Help file on VLOOKUP.

The key point here is that you gave us specific columns to work with and I provided a formula based on that information. If Sheet1 A & F and Sheet2 A & H are not the columns that you are using, then the formula won't work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
June 13, 2011 at 13:17:54
actually, it did work. I just had to put my sheet name in quotations.

Thanks for your help!!


Report •


Ask Question