=IF(ISNUMBER('Sheet2'!G4:G46), 'Sheet2'!C4:C46, ?)

It sounds like this is what you want...

rng1 refers to Sheet2!G4:G46
rng2 refers to Sheet2!C4:C46

Lets assume you want the results listed starting in cell A1.

Array entered** in cell A1:

=IF(ROWS(A$1:A1)<=COUNT(rng1),INDEX(rng2,SMALL(IF( ISNUMBER(rng1),ROW(rng2)),ROWS(A$1:A1))-MIN(ROW(rng2))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get blanks.

--
Biff
Microsoft Excel MVP


"Austin" wrote in message
...

I have two sheets in one excel document. I need to pull a parts
description
from sheet 2 to sheet 1 only if sheet 2's part description has a numeric
value for that part in a different cell on the same row. I am using the
=If(IsNumber.. formula and it works in pasting the values.

What I need is to know if the IsNumber is false, how do I move to the next
line to check for a true value? In other words, I don't want to populate
sheet 1 with a value unless it is a true statement. I want to consolidate
my
parts list onto sheet 1 by referencing the numeric value for that part on
sheet 2.

Here is the formula I have with the ? denoting the area I don't know.
Seems
I need a statement that will force the formula to try the next line but
still
paste into the original sheet1 cell until a value is returned true and
then
move to the next line.
=IF(ISNUMBER('Sheet2'!G4:G46), 'Sheet2'!C4:C46, ?)

Help?

-Austin

Configuration: Microsoft Excel 2007