I've got a file with two worksheets...the first sheet has team names in the
first column in non-consecutive rows (there's other information about each
team on the rows in between) and I want to transfer those team names to
consecutive rows in the second worksheet with a formula (so that when the
teams change, the second sheet does too) but I want the flexibility of adding
additional rows between the team names without effecting the working of the
second sheet. The team names are the only information pieces in the first
column.

in the new sheet in cell A1 enter
=Sheet1!A1
assuming your names are in Col A in Sheet1

Copy it down till you cover much more that what is used in Col A in Sheet 1
(to accommodate future additons)

Filter on Non-blanks... this way you will always see team names even if you
add new ones in Sheet 1...

"Allen" wrote:

I've got a file with two worksheets...the first sheet has team names in the
first column in non-consecutive rows (there's other information about each
team on the rows in between) and I want to transfer those team names to
consecutive rows in the second worksheet with a formula (so that when the
teams change, the second sheet does too) but I want the flexibility of adding
additional rows between the team names without effecting the working of the
second sheet. The team names are the only information pieces in the first
column.

Filter on non-blanks? I'm not sure what that means...

Anyway, unless the Filter on non-blanks takes care of this, information on
Sheet1 may be in A1, A6, A10, A15, A20, A22, A25, etc and I want that
information copied to A1, A2, A3, A4, etc.

Thanks.

"Sheeloo" wrote:

in the new sheet in cell A1 enter
=Sheet1!A1
assuming your names are in Col A in Sheet1

Copy it down till you cover much more that what is used in Col A in Sheet 1
(to accommodate future additons)

Filter on Non-blanks... this way you will always see team names even if you
add new ones in Sheet 1...

"Allen" wrote:

I've got a file with two worksheets...the first sheet has team names in the
first column in non-consecutive rows (there's other information about each
team on the rows in between) and I want to transfer those team names to
consecutive rows in the second worksheet with a formula (so that when the
teams change, the second sheet does too) but I want the flexibility of adding
additional rows between the team names without effecting the working of the
second sheet. The team names are the only information pieces in the first
column.

Allen,

My earlier solution will not work. Pl. try this...
[Solution originally given by MAX in another post]

Assuming your names are in Sheet 1 Col A
1. In Sheet 2 Col A enter
=IF(Sheet1!A1="","",ROW())

2. In Sheet 2 Col B enter
=IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A ,ROW())))

Hope this works

"Allen" wrote:

Filter on non-blanks? I'm not sure what that means...

Anyway, unless the Filter on non-blanks takes care of this, information on
Sheet1 may be in A1, A6, A10, A15, A20, A22, A25, etc and I want that
information copied to A1, A2, A3, A4, etc.

Thanks.

"Sheeloo" wrote:

in the new sheet in cell A1 enter
=Sheet1!A1
assuming your names are in Col A in Sheet1

Copy it down till you cover much more that what is used in Col A in Sheet 1
(to accommodate future additons)

Filter on Non-blanks... this way you will always see team names even if you
add new ones in Sheet 1...

"Allen" wrote:

I've got a file with two worksheets...the first sheet has team names in the
first column in non-consecutive rows (there's other information about each
team on the rows in between) and I want to transfer those team names to
consecutive rows in the second worksheet with a formula (so that when the
teams change, the second sheet does too) but I want the flexibility of adding
additional rows between the team names without effecting the working of the
second sheet. The team names are the only information pieces in the first
column.