Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel If statement copy only if ISNumber result is yes
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel If statement copy only if ISNumber result is yes
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If, And, Isnumber statement | Excel Discussion (Misc queries) | |||
copy paste SQL query result to excel spreadsheet with formatting | Excel Worksheet Functions | |||
IF statement when formula result is blank | Excel Worksheet Functions | |||
IF statement result | Excel Worksheet Functions | |||
How do I make a sound as a result of an "if" statement in excel | Excel Worksheet Functions |