I need a formula to
1) look in colunm a, gather any numbers present in that colunm, even though
most wil be empty
2) then i want them to take that list, get rid of the empty cells and put
that data either into a new table or on another sheet.
thanks.

Sounds like a sort would work. If you want a function, try this:
=IF(ROWS($1:1)<=COUNTA(A1:A20),INDEX(A1:A20,SMALL( IF(A1:A20<"",ROW(A1:A20)-MIN(ROW(A1:A20))+1),ROWS($1:1))),"")

Enter with Ctrl+Shift+Enter, not just enter...

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"cent" wrote:

I need a formula to
1) look in colunm a, gather any numbers present in that colunm, even though
most wil be empty
2) then i want them to take that list, get rid of the empty cells and put
that data either into a new table or on another sheet.
thanks.

Hi,

Select the range and press Ctrl+G Special Constants. This will select
all the non blank cells. If the non blank cells are the result of formulas,
then select formulas. Now copy the selected values and paste it in a new
range

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"cent" wrote in message
...
I need a formula to
1) look in colunm a, gather any numbers present in that colunm, even
though
most wil be empty
2) then i want them to take that list, get rid of the empty cells and put
that data either into a new table or on another sheet.
thanks.

You can also use the autofilter to filter the nonblank cells, copy and paste
to a new sheet or new range.
No formula needed.


"cent" wrote:

I need a formula to
1) look in colunm a, gather any numbers present in that colunm, even though
most wil be empty
2) then i want them to take that list, get rid of the empty cells and put
that data either into a new table or on another sheet.
thanks.