IF Then across 2 sheets

[diggory]

Hey, having trouble figuring out how to run this code. I'm not nearly as excel savy as most of you so bare with me...

I want to reference sheet1 A!4 with sheet3 A!4. If these values match, I want to "plug in" the values from the corresponding row on sheet 3 into sheet 1.

For example. IF sheet1 A5 = sheet3 A9... then sheet3 E9, sheet3 F9, sheet3 G9 should be inserted into sheet1 H5, sheet1 I5, sheet1 J5 respectively.

If A5 does not equal any values in sheet 3 A# then leave H I J blank.

I have been trying to run as a macro under visual basic. Here is what I have started, but it has not gotten me anywhere:

If Value(Sheet1!A!4)= Value(Sheet3!A!4) Then
value(sheet1! H!4)=Sum(sheet3! E!9),
value(sheet1! I!4)=Sum(sheet3! F!9),
value(sheet1! I!4)=Sum(sheet3! G!9),
ElseIf (Value(Sheet1!A!4)<>Value(sheet3!A!4)," ")
End If

 

[mikerickson]

In Sheet1!H5 you could put the formula =VLOOKUP($A5, Sheet3!$A$1:$G$1000, 5, False)
In Sheet1!I5, =VLOOKUP($A5, Sheet3!$A$1:$G$1000, 6, False)
In Sheet1!J5, =VLOOKUP($A5, Sheet3!$A$1:$G$1000, 7, False)

 

[diggory]

Bump. Still having lots of trouble with this one, getting lots of errors. Excel dummy?

 

[mikerickson]

The #NA errors are probably when the entry in Sheet1!A5 is not in Sheet3!$A:$A

 

[Akinrotimi]

If you don't want to see the errors,then make use of this modification to the formular proffered by mikerickson:

In Sheet1!H5 you could put the formula =iferror(VLOOKUP($A5, Sheet3!$A$1:$G$1000, 5, False),"")
In Sheet1!I5, =iferror(VLOOKUP($A5, Sheet3!$A$1:$G$1000, 6, False),"")

In Sheet1!J5, =iferror(VLOOKUP($A5, Sheet3!$A$1:$G$1000, 7, False),"")

If the formular does not see the value it would return blank.

I hope this helps.

Regards

Rotimi

 

[diggory]

I am getting errors with the =iferror function, but I will keep playing with it. Starting to get it to work now.

Is it possible to use the same function for searching for a name? Ie, sheet1 C4=John D4=Doe, find John Doe in sheet2 C and D and fill respective value of sheet2 column 5 into sheet1 E? I am getting #N/A all the way down when I try this.

 

[Akinrotimi]

IFERROR is a function which works with any other formular.Try remembering the following equation where "F" refers to the regular formulae you might be using:

IFERROR(F,"")

Try this with some basic formular so that you get used to it.

Regards

Rotimi

 

[diggory]

I am trying to use the following formula which references a cell with a name (ie, Larry) and searches it in sheet2 to input data as a time in the row for Larry.

=iferror(VLOOKUP($C4, Sheet2!$A$1:$D$1000,5, FALSE),"Sheet2!")

I am getting only #NAME? errors. any idea?

 

[mikerickson]

If you are using something before Excel 2007, that formula should be
=IF(ISERROR(VLOOKUP($C4, Sheet2!$A$1:$D$1000,5, FALSE)), "Sheet2!", VLOOKUP($C4, Sheet2!$A$1:$D$1000,5, FALSE))

 

[diggory]

Mikerickson, that forumula just says "Sheet2!" in the cell if the name was found. How can I get it to input data from the respective row it found the name in?

And yes, I am using 2003... Darn work computers. smh