another combine lists question

[crs]

I have another question about combining 2 excel lists into 1 list

scenario: I have a workbook with 3 sheets
Sheet 1 has a list of students in column A
Sheet 2 has a list of some more students in column A
(completely different from that of

Sheet 1)

Question: I want to combine the 2 lists into 1 single list of all
students onto Sheet 3 without just
copying and pasting because the lists will continually change and may
increase or decrease in size.

Any solutions.....

Thanks in advance!

CRS

 

[Scott]

It depends on whether or not you want it sorted. If you just want a
list:
=IF(ROW(A2)-ROW($A$1)<=(COUNTA(Sheet1!$A:$A)-1),OFFSET(Sheet1!$A$1,ROW(A2)-ROW($A$1),0),IF(ROW(A2)-ROW($A$1)<=((COUNTA(Sheet1!$A:$A)-1)+COUNTA(Sheet2!$A:$A)-1),OFFSET(Sheet2!$A$1,ROW(A2)-ROW($A$1)-(COUNTA(Sheet1!$A:$A)-1),0),""))

This assumes that you have a header row on each sheet. (The result is
not sorted)

Scott

 

[crs]

It depends on whether or not you want it sorted. If you just want a
list:

Ideally, sorted, but this is a start. Thanks for the quick reply.

CRS

 

[crs]

Actually I don't need it sorted, because I have them sorted already and
I don't want the combined list sorted. I need it just way it is.
Thanks - works great.

CRS