Copy a Row to another sheet of a cell in a column is highlighted

**kdsanderson** · 04-30-2013, 10:37 AM

Hello,

I have a worksheet that is used to record incidents. We have a conditional format to recognize if the same name is entered into column C (repeat offender etc). I would like to pull the ENTIRE row of this (repeat offender) to a new sheet. Basically there are columns A:M. The Name is column C and the worksheet goes from 2:300. I can NOT use VBA, without an explanation, but I have tried every formula I can think of and found nothing. The conditional formatting is set to turn the Duplicate cells in column C RED (highlighted) with black text. I only want to pull it to the new worksheet if the C cell is highlighted red fill. Can anyone help me with this?

**benishiryo** · 04-30-2013, 11:06 AM

hi kdsanderson, welcome to the forum. there are no formulas to recognise colours, but thankfully you pointed out the reason for the colours. see if the file in sheet2 helps. it is an array formula. that means you cannot key in & press ENTER. you must press CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Formula:

Please Login or Register  to view this content.

and i purposely left column M with some blanks. if you have such a scenario & dont want sheet2 to show as 0 (it will return 0 if it finds a match but cell is empty), then format the cells like the screenshot. whatever format it has, it must have 2 semi-colons. fyi, the semi-colons are for separate parts to treat +ve numbers, -ve numbers, blanks. so i want the blanks as nothing.

**kdsanderson** · 04-30-2013, 11:52 AM

I attached an example below, but I guess I am the type of user that needs to understand the formula to enter it properly. I could not get yours to work, but I greatly appreciate your assistance. I guess what I am trying to do is IF the name in column C is a Duplicate I want to copy the ENTIRE row to Sheet 2.