Automatically copy cells content based on the content of another cell.

**chrisbarlow1984** · 02-01-2014, 09:58 AM

Hi i hope someone can help me. i want to be able to automatically generate a list of students who are absent on a given day based on the absence of a "y" in another cell.

A colleague has suggested i could do this with VLOOKUP but i havent been able to find the answer online.

i have attached a sample workbook if that is useful.

Thanks in advance!!

Chris

**mrice** · 02-01-2014, 11:12 AM

Try this (Attached) - the macro code is on the Sheet1 tab in the VBA editor (Alt F11)

**XOR LX** · 02-01-2014, 11:13 AM

Hi and welcome to the forum!

Based on your attachment, this array formula** in B14:

=IF(ROWS($1:1)>COUNTIF($B$2:$B$9,""),"",INDEX($A$2:$A$9,SMALL(IF($B$2:$B$9="",ROW($B$2:$B$9)-MIN(ROW($B$2:$B$9))+1),ROWS($1:1))))

Copy down until you start to get blanks for the results.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).