Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Copy To Last Row

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have a workbook that has 2 sheets, "Roster" and "Team".
I want to be able to copy specific cells from the current row in Roster to the 1st blank row in Team.
From current row Col A to Team Col E
B to C
C to B

I have attached an example.
Any help would be appreciated.


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics

I am not very good at writing macros but this is a simple one and I don't know why it wont work, It is meant to request a number and a team find the player matching the number in the list and move it to that team


Sub Transfer()

PlayerID = InputBox("Enter Player ID", "Select Player", "")
TeamID = InputBox("Enter Team ID", "Select Team", "")

Sheets("Team " & TeamID).Select

If Range("N1") < 32 Then

Sheets("Player Pool").Select
For player = 1 To 90
If Cells(plrow + 1, 1) = PlayerID Then
Range(Cells(plrow + 1, 2), Cells(plrow + 1, 9)).Cut
Sheets("Team " & TeamID).Select

For Roster = 1 To 32
If Cells(Roster + 1, 4) = "" Then
Cells(Roster + 1, 4).Select
End If
Next Roster

End If

Next player

Response = MsgBox("No free spots in roster")
End If

End Sub

It works if the first If function is false, but if it is true nothing happens.

Dear Friends,

I've just a Tast of Excel and not much of an Advance user. I've a Problem I wanna ask you guys. Theres this weekly Roster which is shared to our team by the Roster making team. The Roster follows certain pattern that is there are like for example 5 Teams working in a Unit.

Roster Goes like this.
21-Feb 22-Feb 23-Feb 24-Feb 25-Feb 26-Feb 27-Feb TL Mon Tue Wed Thu Fri Sat Sun
A1 M1 M1 M1 M1 M1 M1 OFF B1 M M M M OFF OFF M C1 M1 M1 M1 M1 OFF M1 M1 D1 M M OFF OFF M M M E1 M1 OFF M1 M1 M1 M1 M1

OK TL Means Team Name.. Ofcourse I changed them. Now the Above roster follows a Pattern where for the Next Week, A1 Team will Follow the B1 Roster and B1 will Follow C1 for their Next Week and C1 For D1 and so Forth. And whole year the Same patterns repeats itself. Now Simple copy pasting is a Bit Difficult and Error ****. Keeping in mind that we deal with atleast 50+ Teams. I've just mentioned 5 here for the sake of example.

My Question is, Is there anyway I can automate this task in Excel. I'd appreciate your kind response...


Hello, I'm trying to see if there is a way to get Formula to look up 2 (Vlookup formulas to get 1 result)? Then in part 2, I'm trying to get a Team Points and Opponents Points total. When I try to do a "SUM" in cell AF4, =SUM(T4,V4,X4,Z4,AB4,AD4) and also in cell AG4, =SUM(U4,W4,Y4,Z4,AA4,AC4) It would give me a #Value! or a "0" error.

Example: I have a Tournament of 20 teams, and each Team plays each other and I have the layout like this.......
"Team A" (B4) "Team A" Score (C4) "Team B" (D4) "Team B" Score (E4)
"Team C" (B5) "Team C" Score (C5) "Team D" (D5) "Team D" Score (E5)
"Team E" (B6) "Team E" Score (C6) "Team F" (D6) "Team F" Score (E6) etc.

The result that I want to find is... when the Score is put in the (C4) or (E4) for the First game, I want another section like (I4)&(K4) & (N4)&(P4) to display Team Results for (Points For & Points Against). I have a file I can send you to clear up any confusion that I'm asking.

Thanks for your help,

I have four groups with three teams with different amount of people on the
teams my current code using team 1 as an example is:

Sheets(Array("T1-02", "T1-03", "T1-04", "T1-05", "T1-06", "T1-07",
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Team 1").Select

problem is those who have less get the form with no data. My question is can
I get a message box to enter the number of people on the team and print that?
and how 3 ppl = T1-01 to T1-03, 8 ppl is as you see above. Much thanks

Hi all,

I'm new to the forum and come seeking some help editing a VBA script that a friend helped me with. Regretfully, he's tied up and can't assist right now.

Here is the VBA script as it exists today:


Sub InsertSheets()
Application.ScreenUpdating = False

    'define variables used in script
    Dim oNewSetNum As Double
    Dim oPrevSetNum As String
    'look at previous sheet to determine set number
    Sheets("Project Notes").Select
    On Error Resume Next
    If ActiveSheet.Name = "Project Notes" Then
        oNewSetNum = 1
    End If
    If ActiveSheet.Name <> "Project Notes" Then
        oPrevSetNum = Mid(ActiveSheet.Name, 8, (Len(ActiveSheet.Name) - 19))
        oNewSetNum = CDbl(oPrevSetNum) + 1
    End If
    'unhide template sheets
    Sheets("Sprint # Team Roster").Visible = True
    Sheets("Sprint # Backlog").Visible = True
    Sheets("Sprint # Burndown").Visible = True
    'copy template sheets and move to starting location
    Sheets(Array("Sprint # Team Roster", "Sprint # Backlog", "Sprint # Burndown")). _
        Copy After:=Sheets(2)
    'rename sheets
    Sheets("Sprint # Team Roster (2)").Name = "Sprint " & oNewSetNum & " Team Roster"
    Sheets("Sprint # Backlog (2)").Name = "Sprint " & oNewSetNum & " Backlog"
    Sheets("Sprint # Burndown (2)").Name = "Sprint " & oNewSetNum & " Burndown"
    'hide template sheets
    Sheets(Array("Sprint # Team Roster", "Sprint # Backlog", "Sprint # Burndown")). _
    ActiveWindow.SelectedSheets.Visible = False

    'change tab color to gray on previous set of sheets
    If oPrevSetNum >= 0 Then
        Sheets("Sprint " & oPrevSetNum & " Team Roster").Tab.ColorIndex = 15
        Sheets("Sprint " & oPrevSetNum & " Backlog").Tab.ColorIndex = 15
        Sheets("Sprint " & oPrevSetNum & " Burndown").Tab.ColorIndex = 15
    End If
    Sheets("Product Backlog").Select

Application.ScreenUpdating = True

End Sub

What I need to do is add to this the ability to also add a formula to a sheet called Estimate Reference in the first available column. This formula should look like this: =IF($A2>="","",(SUMIF('Sprint 1 Backlog'!$A$8:$A$2000,$A2,'Sprint 1 Backlog'!$D$8:$D$2000))) but where it says 'Sprint 1 Backlog' it uses the oNewSetNum number. The Macro also needs to fill the column name from the previous column and add 1 to it. So if the previous column was Sprint 1, the new column would be Sprint 2. Lastly, it needs to fill this down to the equal length of previous column... Potentially something like this:

    Selection.AutoFill Destination:=Range("###2:###2001")

where the ### is the column that we just put in.

I hope that this properly explains what I'm needing to do and appreciate any assistance you can provide.

Chris M.
excel padawan

I'm new to macros and VB and believe the end solution VBA. I have attached an example file.

I desire the end result of:
Copy and paste each row from "Roster" worksheet to the other worksheets where "LO1" or "LO2" or "LO3" or "LO4" is found in the row.

For example, since row 2 contains "LO3", then this row needs copy/paste to worksheet "LO3". Since row 4 contains "LO3", it would be copy/pasted or appended to "LO3" tab underneath the row 2 that was pasted previously.

Note: It is possible each row may contain multiple location IDs but not likely.

I would like to:
- maintain the "Roster" worksheet with no changes.
- maintain the header row from "Roster" worksheet in each of the LO1..LO4 worksheets.
- maintain formatting (font, highlights) for visual aid to others viewing the worksheets.
- allow for changes to "Roster" worksheet and possibly have a button displayed to run macro after updates are completed to the Roster tab.

Any assistance is much appreciated. I haven't progressed very far with the VB code for this challenge.

Thank you.

i've written this code to obtain a list view but i cannot visualize all
data contained in my list.
everytime i launch my macro i get an "1004 run time error", i can't
understand why.
(surely because my lack of knowledge about list view & tree view

the structure i want to obtain is very simple, here follow a little


etc etc etc

Actually i can visualize only 10 of my 15 categories... is this a
limitation of list view or there's someting wrong in my code...... ???

this is what i've written

Private Sub UserForm_Initialize()
Dim player1 As String
Dim player As String

With TreeView1.Nodes
' player1 = Worksheets("team").Range("H3").Value
player1 = "Team"
.Add , , "KEY_1", player1

For i = 4 To 25
player = Worksheets("team").Range("H" & i - 1).Value
' add other "father-nodes"

.Add "KEY_" & i - 3, tvwNext, "KEY_" & i - 2, player
' add "child nodes"

.Add "KEY_" & i - 2, tvwChild, "KEY_A" & i - 2,
Worksheets("team").Range("W2").Value & ": " &
Worksheets("team").Range("W" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_B" & i - 2,
Worksheets("team").Range("X2").Value & ": " &
Worksheets("team").Range("X" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_C" & i - 2,
Worksheets("team").Range("Y2").Value & ": " &
Worksheets("team").Range("Y" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_D" & i - 2,
Worksheets("team").Range("Z2").Value & ": " &
Worksheets("team").Range("Z" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_E" & i - 2,
Worksheets("team").Range("AA2").Value & ": " &
Worksheets("team").Range("AA" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_F" & i - 2,
Worksheets("team").Range("AB2").Value & ": " &
Worksheets("team").Range("AB" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_G" & i - 2,
Worksheets("team").Range("AC2").Value & ": " &
Worksheets("team").Range("AC" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_H" & i - 2,
Worksheets("team").Range("AD2").Value & ": " &
Worksheets("team").Range("AD" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_I" & i - 2,
Worksheets("team").Range("AE2").Value & ": " &
Worksheets("team").Range("AE" & i - 1).Value
.Add "KEY_" & i - 2, tvwChild, "KEY_J" & i - 2,
Worksheets("team").Range("AF2").Value & ": " &
Worksheets("team").Range("AF" & i - 1).Value
' .Add "KEY_" & i - 2, tvwChild, "KEY_K" & i - 2,
Worksheets("team").Range("I2").Value & ": " &
Worksheets("team").Range("I " & i - 1).Value
' .Add "KEY_" & i - 2, tvwChild, "KEY_L" & i - 2,
Worksheets("team").Range("S2").Value & ": " &
Worksheets("team").Range("S" & i - 1).Value
' .Add "KEY_" & i - 2, tvwChild, "KEY_M" & i - 2,
Worksheets("team").Range("T2").Value & ": " &
Worksheets("team").Range("T" & i - 1).Value
' .Add "KEY_" & i - 2, tvwChild, "KEY_N" & i - 2,
Worksheets("team").Range("U2").Value & ": " &
Worksheets("team").Range("U" & i - 1).Value
' .Add "KEY_" & i - 2, tvwChild, "KEY_O" & i - 2,
Worksheets("team").Range("V2").Value & ": " &
Worksheets("team").Range("V" & i - 1).Value

' End If
Next i

End With

End Sub

thank for your help.


NB: i've other questions about this topic... but let's do it step by

I'm trying to figure out how to organize my company's data within a workbook. I have one spreadsheet "In-process Roster" that contains in order by column the Company, Name, SSN, Position, Team, and Phone number organized by company. On the next spreadsheet I would like to make a call roster that shows Team, Name, and Phone Number. Basically I'm trying to make a call roster. What I would ultimately like to achieve is once I inprocess a new employee it will automatically update the call roster by Office. Thank you in advance.


I got some help from Peter the other day, but I still can't get it to work!
Referring to the table attached, I use vlookup to ascertain which team is home and which one is away.

But now I want to automate this process, so that when the date, division, and round number are entered, the vlookup will use say "Division 3 if I have selected that division.

And should I use the vlookup to relate the rnd no: to the date?

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF 2 No: Division 1 No: Division 2 No: Division 3 No: Division 4 3 1 Team 1 1 Team 9 1 Team 17 1 Team 25 4 2 Team 2 2 Team 10 2 Team 18 2 Team 26 5 3 Team 3 3 Team 11 3 Team 19 3 Team 27 6 4 Team 4 4 Team 12 4 Team 20 4 Team 28 7 5 Team 5 5 Team 13 5 Team 21 5 Team 29 8 6 Team 6 6 Team 14 6 Team 22 6 Team 30 9 7 Team 7 7 Team 15 7 Team 23 7 Team 31 10 8 Team 8 8 Team 16 8 Team 24 8 Team 32 11 12 13 14 01/05/10 08/05/10 13/05/10 20/05/10 27/05/10 03/06/10 10/06/10 15 Rnd 1 Rnd 2 Rnd 3 Rnd 4 Rnd 5 Rnd 6 Rnd 7 16 1 v 8 4 v 1 1 v 7 3 v 1 1 v 6 2 v 1 1 v 5 17 2 v 7 5 v 3 8 v 6 4 v 2 7 v 5 3 v 8 6 v 4 18 3 v 6 6 v 2 2 v 5 5 v 8 8 v 4 4 v 7 7 v 3 19 4 v 5 7 v 8 3 v 4 6 v 7 2 v 3 5 v 6 8 v 2 20 21 17/06/10 24/06/10 31/6/10 06/07/10 13/07/10 20/07/10 27/07/10 22 Rnd 8 Rnd 9 Rnd 10 Rnd 11 Rnd 12 Rnd 13 Rnd 14 23 8 v 1 1 v 4 7 v 1 1 v 3 6 v 1 1 v 2 5 v 1 24 7 v 2 3 v 5 6 v 8 2 v 4 5 v 7 8 v 3 4 v 6 25 6 v 3 2 v 6 5 v 2 8 v 5 4 v 8 7 v 4 3 v 7 26 5 v 4 8 v 7 4 v 3 7 v 6 3 v 2 6 v 5 2 v 8 27 28 29 30 31 32 33 Date 34 Division 35 Round Number 36 Home Team Number: 6 37 Away Team Number: 3 38 Home Team Team 6 Away Team Team 3

I have numbered and lettered the cells so you can catch my drift.
The red cells are the variables, and the green cells are where the formulas are.

Any assistance with this would be greatly appreciated!


Hi Guys,

I have been trying to figure this one out for a while, with no joy.

I have a massive list of figures, that look something like this;

Column A - Column B - Column C - Column D
Team A - 2 - Team B - 1
Team C - 1 - Team D - 2
Team E - 0 - Team F - 3
Team G - 1 - Team H - 2
Team B - 3 - Team C - 1
Team G - 0 - Team A - 2
Team F - 1 - Team D - 0
Team H - 5 - Team E - 3
Team G - 2 - Team A - 2
Team B - 2 - Team F - 1
Team D - 0 - Team E - 1
Team C - 3 - Team H - 1
Team F - 1 - Team D - 3
Team A - 1 - Team B - 4
Team H - 0 - Team C - 1
Team E - 5 - Team G - 1

What I want to be able to do is select Home and Away, and for the last 6 home, last 6 away scores, then last 10, then last 20

For example, on the table above, if I selected Team E as Home and Team B as away, the results would be returned as;

Team E - Last - 5
Team E - 2nd Last - 0
Team E - 3rd Last - etc etc etc

Team B - Last - 4
Team B - 2nd Last - 1
Team B - 3rd Last - etc etc etc

Obviously, if the list above were longer, the

I have changed the colours in the table above to try to explain better.....

Can this be done?? Either by formula, or in VBA??

Thanks in advance

I am trying to do a European Champions League spreadsheet and i want to be able to enter the scores and the table to automaticly update, i have looked up the function Vlookup, dut do not get how to use the function.

So can anybody help me with it

the fixtures
Team A V Team B 1-1
Team C V Team D 1-0
Team B V Team D -
Team C V Team A -
Team A V Team D -
Team B V Team C -
Team C V Team B -
Team D V Team A -
Team A V Team C -
Team D V Team B -
Team B V Team A -
Team D V Team C -

I am trying to do a European Champions League spreadsheet and i want to be able to enter the scores and the table to automaticly update, i have looked up the function Vlookup, dut do not get how to use the function.

So can anybody help me with it

the fixtures
Team A V Team B 1-1
Team C V Team D 1-0
Team B V Team D -
Team C V Team A -
Team A V Team D -
Team B V Team C -
Team C V Team B -
Team D V Team A -
Team A V Team C -
Team D V Team B -
Team B V Team A -
Team D V Team C -

Dear Excel(lent) users,

I have a query concering the following.

In an excel sheet I have a number of columns, which contains teamnames (team
1...Team N)

I am using Columns A through J to indicate which team is working on an
issue. Now I want to know which is the actual current team working on the

For instance:
Issue 1 has been worked on by three teams (1,2 and 3) in the following order:
A1 = Team 1
B1 = Team 3
C1 = Team 2
In this instance Team 2 is the current team working on the issue

Another example:
A1 = Team 1
B1 = Team 5
C1 = Team 7
D1 = Team 2
E1 = Team 1
F1 = Team 3
In this instance Team 3 is the current team working on the issue

So what I am looking for is the last entry in the (horizontal) sequence.

Hope this makes sense.

** Fool on the hill **

Hello Excelers ,

I need one of you to help me out with this one?

Attached is a small example of a larger spreadsheet, I print out for each team their areas, so the print area is from column B to column H. I need a formula in B1 to give me the Team name when I filter the list, for example if I sort by Team C, I want "Team C" to be displayed in B1 or sort by Team D, "Team D" to be displayed. Any ideas?


So this is sort of a two part question... in the below snippet i have a row getting filled in with dates for each day for two years...that part seems to work fine. the next part of it will check the value it just entered, and if it equals a value from a different sheet, i want to select that column and apply a colored border to it. i think the method is .borderAround but not sure, i havent gotten that far... I'm getting a run time error 1004 'application defined or object defined error, here is the snippet


lastRow = Worksheets("Team Roster").Range("A65536").End(xlUp).Row
    For iColumn = 4 To 734          '730 days in two years, started with 4 because that was the first column to fill automatically
        Worksheets("Team Roster").Cells(5, iColumn).Value = Worksheets("Team Roster").Cells(5, iColumn - 1).Value + 1
        If Worksheets("Team Roster").Cells(5, iColumn).Value = Worksheets("Setup").Range("S8").Value Then
            Worksheets("Team Roster").Range(Cells(3, iColumn), Cells(lastRow, iColumn)).Select
        End If
    Next iColumn

Hi, I am hoping one of you guys will be able to help.

Bascially I have a Master Sheet that contains rows of data each with column headings

Column A is the Team Name
Column B is a Value
Column C is a Date

Throughout this sheet there can be mutiple instances of a Team Name (e.g. Team 1, Team 2 etc) with various values and dates


Team 1, 123, 19/09/10
Team 2, 345, 20/09/10
Team 3, 999, 20/09/10
Team 2, 456, 21/09/10
Team 1, 222, 20/09/10
etc etc

I then have separate tabs for each of these Teams

What i want to do is on each of these Team tabs, lookup each occurence of the team name on the Maste Sheet (e.g Team 1 tab will search for all occurences of "Team 1" in Column A on the Master Sheet) and return the Value in one column and the Date in another column.

I have used VLOOKUP but this only returns the first occurence of Team 1 it finds

Is there anything that will find ALL occurence of "Team 1" and list them on consecutive rows in the Team 1 tab?

I hope this makes sense

Any help will be very much appreciated



I'm new to all of this, and it can become a bit daunting at times, but what I'd like is some help in setting up a formula to enable the correct reference when multiple cells are used as references.

As you can see, I have 4 divisions in a local eight ball pool comp.
Within each division are 8 teams.
And then the table below it shows when team 1 for example, is to play team 8.
And then 2 weeks later, team 1 then plays team 7.
I want this formula to work with another excel sheet, so that when I put in the division, date, and home team number, the answer will appear in another cell.

Now we'll assume that cells A2:A9 Contain the team numbers,
Cell B2:B9 contains division 1 teams,
Cell C2:C9 division 2 teams and so on.

Any help with working this out would be much appreciated.

Division 1 Division 2 Division 3 Division 4 1 Team 1 Team 9 Team 17 Team 25 2 Team 2 Team 10 Team 18 Team 26 3 Team 3 Team 11 Team 19 Team 27 4 Team 4 Team 12 Team 20 Team 28 5 Team 5 Team 13 Team 21 Team 29 6 Team 6 Team 14 Team 22 Team 30 7 Team 7 Team 15 Team 23 Team 31 8 Team 8 Team 16 Team 24 Team 32 Round 1 Round 2 Round 3 15th Feb 22nd Feb 1st Mar 1 V 8 4 V 1 1 V 7 2 V 7 5 V 3 8 V 6 3 V 6 6 V 2 2 V 5 4 V 5 7 V 8 3 V 4

I have three columns, with "Home team", "Away team" and "Winner"

"Winner" can either be "H" (home), "A" (away) or "D" (draw)

So essentially my SS looks something like:

A / B / H
C / D / A
B / A / H
D / C / H
C / A / D
B / D / A

etc. as the list is continually updated.

I'm trying to calculate streaks.

For example, I would like to create a formula that calculates team A's win percentage over its past 10 games.

Using the SUM function and arrays, I have created a "stream" of results (i.e. 1,-1,1,-1,0,1,1,etc.) for team A in cells I21 to AD21, but this will continue to expand in the future.

Anyone smarter than me have any ideas?

H Team 30/12/07 $1.70 LOSS - no cnt > $1.60
H Team 30/12/07 $1.42 DRAW- 1st cnt-rules met
H Team 30/12/07 $1.47 LOSS - no cnt-1.47>1.42
H Team 01/01/08 $1.45 LOSS - 2nd cnt-rules met
A Team 02/01/08 $1.54 WON - no cnt-away game
H Team 02/01/08 $1.30 WON - no cnt < $1.40
H Team 03/01/08 $1.50 WON - "2" displayed
H Team 20/01/08 $1.41 DRAW - 1st cnt-rules met
H Team 20/01/08 $1.53 DRAW - no cnt-1.53>1.41
A Team 21/01/08 $1.59 LOSS - no cnt-away game
H Team 22/01/08 $1.53 LOSS - 2nd cnt-rules met
H Team 30/01/08 $1.45 DRAW - 3rd cnt-rules met
H Team 31/01/08 $1.42 LOSS - 4th cnt-rules met
H Team 01/02/08 $1.57 WON - "5" displayed
H Team 05/03/08 $1.45 WON - "1" displayed

What I'm trying to do is choose only one footy match for each unique date that meets my parameters to bet on, and to keep count of the amount of bets I place before I win!

The parameters are as follows;

* Have to be the lowest odds (ColD) but > than $1.40 and < $1.60.
* Be a home game (ColA).
* In the advent of same odds & same date, the team with the lowest alphabetical ranking should be counted.

As you can see in the last column the " " is the data I want displayed.
It shows that it took 2 legitimate bets before I posted my first win, hence "2" is displayed.
It takes another 5 legitimate bets before I get my next WIN, so "5" displayed.
The the very next bet I get another WIN straight up, so "1" is displayed.

Regards Peter

I'm trying to create an excel workbook that can create team rosters.

In worksheet 1, I have a list of all the players. IN subsequent worksheets, I have rosters built with each teams specific information.

On draft day, I want to put the team number that drafted the player in the column next to the name on worksheet 1 and have his name autopopulate on the respective roster worksheets.
I can use If and Vlookup but this leaves gaps between the players names in the worksheet

Any ideas?


I have a situation where the first few columns in my sheet refer to a particular team of people, then the next few columns to a second team, etc.

My problem is that the teams can (and do) change frequently.
Thus one month team A might have 3 members & team B has 6 members while next month team A has 5 members and team B has 4 members.

I want to display the team name spread across the top of however many columns are in use this month...

I currently have my sheet set up such that I have the correct index number for the required team at the head of EVERY column, this in turn can be used to select the required name from a list. BUT this gives team name repeated in every column, and with the columns being narrow all I get is "TeaTeaTea" rather than "Team X"

How can I (automatically) have the "Team X" header displayed across the appropriate number of columns regardless of the varying number of members in the team?

If I have a series of data:

Col. A Col. B
Team 1
Team 2
Team 2
Team 1
Team 3
Team 5
Team 3
Team 4

In column A say I have 50 entries with 5 teams where there is alot of duplicates, is there a formula that I can put in B1 (assuming Team 1 is in cell A1) that will look in column A, and list only the values without the duplicates, looking like this:

Col. A Col. B
Team 1 Team 1
Team 2 Team 2
Team 2 Team 3
Team 1 Team 4
Team 3 Team 5
Team 5
Team 3
Team 4

Ok I have been trying to find some software that will help me with this, but the average price is $100 and I don't want to spend that kind of money if I don't know if it will work.

What I am trying to do is create a schedule for a 20 team fantasy baseball league.

We have 4 divisions of 5 with a 21 weekly match ups.

The divisions are as such:
Cyan: Team 1, Team 2, Team 3, Team 4, Team 5
Magenta: Team 6, Team 7, Team 8, Team 9, Team 10
Yellow: Team 11, Team 12, Team 13, Team 14, Team 15
Black: Team 16, Team 17, Team 18, Team 19, Team 20

Each team will play the teams in their division twice, and the rest of the schedule is filled in with non divisional teams that you won't play more than once.

I don't know if anyone can help with this, but every time I have had a problem that I couldn't figure out in Excel someone here has been able to help.

Let's say I have "teams" of individuals, where each individual is allowed to be on more than one team. Now I want to answer the question, "Name everybody that is on a team with Rick". How could I generate such a list? Below is an example:


Team Person_1 Person_2 Person_3
Team A Joe Chris
Team B Lee Fred
Team C Bob Henry Chris
Team D Rick Fred

DESIRED SUMMARY TABLE (all individuals associated with person on the far left, below)

Everyone on a team with Bob: Henry Chris
Everyone on a team with Chris: Joe Bob Henry
Everyone on a team with Fred: Lee Rick
Everyone on a team with Henry: Bob Chris
Everyone on a team with Joe: Chris
Everyone on a team with Lee: Fred
Everyone on a team with Rick: Fred

I am new to excel and need an experts help on a simple formula

I want to add the total of B21:B30 and if it is less than the value of C21:C30 then
Display "Team 2 Win" and vice versa.

Team 1 is the B Column
Team 2 is the C Column

This is what I got:
=SUMIF((B21:B30>C21:C30),"Team 1 Win","Team 2 Win")

Was I way off? BTW I am using Excel 2003.

thanks in advance!