3 cell formula

[Guest]

Hi I would like to put a name in B1(Tony) that triggers a total that i get in G26(43)and have the total in G26 (43) come up in cell L6.Changing the name 8 times with G26 number total for each going in L6,L7,L8 and so on. Thanks for any help Karl

 

[Ragdyer]

Half the fun of answering questions in this forum is answering the question.
The other half, is the sense of accomplishment in knowing you actually
figured out what the OP asked in the first place!

I have lost all feelings of accomplishment, pertaining to this question.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Hi I would like to put a name in B1(Tony) that triggers a total that i get

in G26(43)and have the total in G26 (43) come up in cell L6.Changing the
name 8 times with G26 number total for each going in L6,L7,L8 and so on.
Thanks for any help Karl

 

[Bob Phillips]

If I understand, not a formula, but VBA

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRows As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
With Target
If .Value = "Tony" Then
cRows = Cells(Rows.Count, "L").End(xlUp).Row
If cRows < 6 Then cRows = 5
Cells(cRows + 1, "L").Value = Range("G26").Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi I would like to put a name in B1(Tony) that triggers a total that i get

in G26(43)and have the total in G26 (43) come up in cell L6.Changing the
name 8 times with G26 number total for each going in L6,L7,L8 and so on.
Thanks for any help Karl

 

[Sandy Mann]

I could be way off base here because I am usually not very good at either
*half* of the *fun* but the way that I read it , in G26 try:

=(B1="Tony")*(SUM(C1:C25))+(B1="Name2")*(SUM(D125))+(B1="Name3")*(SUM(E1:E
25))+(B1="Name4")*(SUM(F1:F25))+(B1="Name5")*(SUM(G1:G25))+(B1="Name6")*(SUM
(H1:H25))+(B1="Name7")*(SUM(I1:I25))+(B1="Name8")*(SUM(J1:J25))

and then in L6:
=IF($B$1="Tony",$G$26,"")
copied down to L13 with the Names changed to the appropriate ones.

HTH

Sandy