Copy cells with only values to another sheet

Hi,
I want to copy data from a row with 1000 cells having some numbers in few cells, to another sheet such that the numbers appear in continuation in the another sheet. What could be the formula for that?
Thanks in advance.

If i have understood you correctly, this is very simple. You can simply highlight that row then use the Go to window (control and G) to select the cells in the row containing numbers (click Special>constants>leave only the 'numbers' box ticked). You should then be able to simply copy and paste the cells left highlighted into a new worksheet.

Hi,
Thanks for your reply, you understood my concern correctly. Only thing is that same thing I want to put in formula so as to change it automatically when I make any changes in parent sheet.
Thanks again for your efforts.

Actually i posted again the same post with an attachment.
http://www.excelforum.com/excel-gene...her-sheet.html

You can do this a few ways. Array formulas are probably the easiest way. This is an array formula you could enter into cell D8 on Page 1 of your example workbook. Drag it along, and then down.

=IFERROR(INDEX('sheet 2'!$D6:$DM6,1,SMALL(IF(LEN('sheet 2'!$D6:$DM6),COLUMN('sheet 2'!$D6:$DM6)-MIN(COLUMN('sheet 2'!$D6:$DM6))+1,""),COLUMN(A1))),"")

Remember array formulas must be entered using control, shift, & enter.

It worked. Thanks a lot.