Transfering from one sheet
Author Message
Transfering from one sheet

Hi I am making a points list for our youth club and need to find out the
Leader in any of five different sections at the moment
I use the formula for example the member who's name is on b55

In Col. BF
=IF(BD55=BD\$72,"Leader"," ")  -- this is the same formula in all of the Col.
BD12:BD70

-- where BD 55 is the individuals total and BD\$72 is the maximum points of
any of the members

using for BD72 the following
=MAXA(BD11:BD71)

Where column BD is the sums of the rows for each lot of points which are in
C through to BC

The persons names are  in Col. B from b12 to b70

The point that I want to do is pick up the person who is Leader in col.BF --
there may be more than one who is equal leader. and past it into a chart on
another sheet

Question
What do I need to pick up the Leader in Col. BF so as to transfer it to
another sheet?

I hope that you can understand all this

Thanks

Chris Haynes

--

Fri, 25 Jun 2004 02:58:16 GMT
Transfering from one sheet
Hi Chris,

This will transfer all the "Leaders" names from Sheet 1 (= sheet with all
to Column A in another sheet (=here Sheet 2) :

Dim I As Integer
Sheets(1).Activate
For I = 12 To 70
If Range("BD" & I).Value = "Leader" Then
Range("BD" & I).Offset(0, -54).Copy
Sheets(2).Activate
Range("A1").Select
If Range("A1").Value = "" Then
ActiveSheet.Paste
Sheets(1).Activate
Application.CutCopyMode = False
ElseIf Range("A2").Value = "" Then
Range("A2").Select
ActiveSheet.Paste
Sheets(1).Activate
Application.CutCopyMode = False
Else
Range("A1").End(xlDown).Rows.Offset(1, 0).Select
ActiveSheet.Paste
Sheets(1).Activate
Application.CutCopyMode = False
End If
End If
Next I
End Sub

HTH
Eddy

Quote:
> Hi I am making a points list for our youth club and need to find out the
> Leader in any of five different sections at the moment
> I use the formula for example the member who's name is on b55

> In Col. BF
> =IF(BD55=BD\$72,"Leader"," ")  -- this is the same formula in all of the
Col.
> BD12:BD70

> -- where BD 55 is the individuals total and BD\$72 is the maximum points of
> any of the members

> using for BD72 the following
>  =MAXA(BD11:BD71)

> Where column BD is the sums of the rows for each lot of points which are
in
> C through to BC

> The persons names are  in Col. B from b12 to b70

> The point that I want to do is pick up the person who is Leader in
col.BF --
> there may be more than one who is equal leader. and past it into a chart
on
> another sheet

> Question
> What do I need to pick up the Leader in Col. BF so as to transfer it to
> another sheet?

> I hope that you can understand all this

> Thanks

> Chris Haynes

> --

Fri, 25 Jun 2004 04:10:12 GMT
Transfering from one sheet
This following is not a complete answer, but it should give you idea for how it
might be done without writing a vba macro:

Explore the capabilities of the VLookUp function.  You'll need to set up a table
containing the names of the people and their scores. You then "lookup" the name
of the person whose score corresponds to the maximum score. You probably have to
have the table in order of ascending scores.  I also suspect it will only return
the first match.

Quote:

> Hi I am making a points list for our youth club and need to find out the
> Leader in any of five different sections at the moment
> I use the formula for example the member who's name is on b55

> In Col. BF
> =IF(BD55=BD\$72,"Leader"," ")  -- this is the same formula in all of the Col.
> BD12:BD70

> -- where BD 55 is the individuals total and BD\$72 is the maximum points of
> any of the members

> using for BD72 the following
>  =MAXA(BD11:BD71)

> Where column BD is the sums of the rows for each lot of points which are in
> C through to BC

> The persons names are  in Col. B from b12 to b70

> The point that I want to do is pick up the person who is Leader in col.BF --
> there may be more than one who is equal leader. and past it into a chart on
> another sheet

> Question
> What do I need to pick up the Leader in Col. BF so as to transfer it to
> another sheet?

> I hope that you can understand all this

> Thanks

> Chris Haynes

> --

--
Ernstoff, LA, CA, USA

Fri, 25 Jun 2004 04:33:13 GMT

 Page 1 of 1 [ 3 post ]

Relevant Pages