I Am trying to take information from one Sheet where for example cells F8=(blankcell) F10=Break F12=(blankcell) J8=Cleaning J10=waiting and have it automatically copied to another sheet where the cells that are not blank have their contents listed without blanks and without repeating cells. for example
Sheet2
Break
Cleaning
Waiting

So far i tried using huge if statements in the destination cells to try to locate the cells with information in them

=IF(LEN('PPI DATA'!F8)0,'PPI DATA'!F8,IF(LEN('PPI DATA'!F10)0,'PPI DATA'!F10,IF(LEN('PPI DATA'!F12)0,'PPI DATA'!F12,IF(LEN('PPI DATA'!J8)0,'PPI DATA'!J8,IF(LEN('PPI DATA'!J10)0,'PPI DATA'!J10,IF(LEN('PPI DATA'!J12)0,'PPI DATA'!J12,IF(LEN('PPI DATA'!N8)0,'PPI DATA'!N8,IF(LEN('PPI DATA'!N10)0,'PPI DATA'!N10,IF(LEN('PPI DATA'!N12)0,'PPI DATA'!N12,IF(LEN('PPI DATA'!R8)0,'PPI DATA'!R8,IF(LEN('PPI DATA'!R10)0,'PPI DATA'!R10,IF(LEN('PPI DATA'!R12)0,'PPI DATA'!R12,IF(LEN('PPI DATA'!V8)0,'PPI DATA'!V8,IF(LEN('PPI DATA'!V10)0,'PPI DATA'!V10,IF(LEN('PPI DATA'!V12)0,'PPI DATA'!V12,"Working")))))))))))))))

But i can't find a way for the cells to disregard the cells already listed so what i get is
Sheet 2
Break
Break
Break

Also i can only looking through as many cells as i can make if statements for which is 64 but i will need to have it search through up to 600 cells. i'm considering making another sheet so that the information is listed together even with blanks so that maybe if the cells aren't scattered i may be able to use a different function. any suggestions would be greatly appreciated

EggHeadCafe - Software Developer Portal of Choice
Silverlight 2 RC0 Doing Data Part VIII : Using the Threadpool
http://www.eggheadcafe.com/tutorials...0-doing-d.aspx