Copying content

Fabian123456

New Member
Joined
Oct 6, 2014
Messages
1
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!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,832
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,791
Messages
5,524,897
Members
409,610
Latest member
db321

This Week's Hot Topics

Top