Copying content

[Fabian123456]

Hey,

the following problem occured to me using Excel 2007:

In Worksheet 1 I have a list with students (s), workers (w) and kids (k) and their adresses (no specific order). I would now like to create three additional worksheets containing only the adresses of one group (so one with students, one with workers and one with kids).

My problem is that if I insert new people to the original worksheet or if I change its order, the other three specific worksheets are not correct anymore. I tried both the IF-function and the (=worksheet1!A1 - function).

Do you hava any advice for me how the lists keeps up-to-date simultaniously and automatically?


Thank a lot!

 

[jasonb75]

Hi Fabian.

You would need to use an array formula to do that, I've included an example below for you to try, but as you want this to work with inserted records, it might be preferable to do this with a dynamic named range as well.

=IFERROR(INDEX(sheet1!$A$1:$A$100,SMALL(IF(sheet1!$B$1:$B$100="Student",ROW(sheet1!$B$1:$B$100)),ROWS(A$2:A2)),"")

Which must be confirmed as an array formula by using Shift Ctrl and Enter, not just Enter.

The formula is based on:-
The information to return being listed in sheet1, $A$1:$A$100
An identifier (Student / worker / kid) being in sheet1 $B$1:$B$100 (this range appears in 2 places)
The first formula is being entered into A2 on the individual sheet(s), when you edit this range to match your own sheet, note that the format is $A$2:$A2, there is no $ for the row at the end of the range.