comparing or matching corresponding value

M

mary

Hello all.
Is is possible to have a formula or macro that whenever a
specific word is entered into any cell in a given column
it will copy its corresponding cell to another cell?
Eg.
I want anytime john or any selected value is entered in
column B for it to record in column C. D1 is always blank
until a value is add. I am look for all corresponding
value for john and May
A1 B1 C1
John 70 70
PAUL 10
MAY 20 20
KELVIN 10
JOHN 20 20
CHRIST 10
MAY 5 5
JOHN 10 10

I tried this formula but no luck... Please help... thanks
 
R

Rob van Gelder

Mary,

It's unclear exactly what you're after. Here's my interpretation.

Right click the Worksheet tab, View Code. Paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Select Case LCase(Target.Offset(0, -1).Value)
Case "john", "may": Target.Offset(0, 1).Value = Target.Value
End Select
End If
End Sub

Rob
 
M

mary

I want column C:C to look of any match in colum A:A and
report its corresponding value from B:B on column C:C.
Example: in column C, I would like to report all the
corresponding value of john to column C. column C make a
match in column A and return all the value in C.
Thanks

mry,
 
R

Rob van Gelder

Mary,

Sorry - I'm really having a hard time following you...

Here's how I understand it:
You want values in column C to equal column B, but only if column A = "John"

That's what the code I supplied in my previous post does.

If you want a worksheet formula, enter this to C1:
=IF(LOWER(A1)="john",B1,"")


Rob
 
M

mary

Thanks Rod. that is what i wanted. I appreciate your
help. is it possible to write me the code. Actually, i
will like to sub total all value for john. really do not
need the each cell value. Just the corresponding sub
total.
Thanks again
 
R

Rob van Gelder

Mary,

You can use an array formula:

=SUM(IF(LOWER(A1:A500)="john",B1:B500,0))
when you enter this formula, press CTRL+SHIFT+ENTER

Or you may want to investigate the SubTotals feature of Excel.
From the data menu, choose SubTotals. (ensure your list is sorted by name
first)

Rob
 
R

Rob van Gelder

Actually the array formula is a terrible idea for this simple task.
There's a better reply by David to your other e-mail regarding the use of
=SUMIF

Rob
 
Top