PDA

View Full Version : help needed with lookups & conditions



ads_3131
05-08-2012, 02:31 AM
Hello....

Could really do with some expert help with my attached spreadsheet.....

Basically , i want sheet2 to look at the table in sheet1 and pull only the rows out that are our problems..... and place neatly into sheet2 in the formated table....

I know the formula in sheet2 will need to lookup a set range of (F4:F29) as a starting point in sheet1..... where if any text is in this column , then copy that row and place it neatly into sheet2's formatted colum, i just dont know how to implement this?

any help would be appriciated ......

tar

Bob Phillips
05-08-2012, 02:48 AM
Add these formulae

H4: =SMALL(IF(Sheet1!$F$4:$F$29<>"",ROW(Sheet1!$F$4:$F$29)),ROW(A1))
A4: =IF(NOT(ISERROR($H4)),INDEX(Sheet1!B$1:B$29,$H4),"")
B4: =IF(NOT(ISERROR($H4)),INDEX(Sheet1!D$1:D$29,$H4),"")
C4: =IF(NOT(ISERROR($H4)),INDEX(Sheet1!F$1:F$29,$H4),"")
D4: =IF(NOT(ISERROR($H4)),INDEX(Sheet1!H$1:H$29,$H4),"")
E4: =IF(NOT(ISERROR($H4)),INDEX(Sheet1!C$1:C$29,$H4),"")

A4 is an array formula.

Copy these all down to row 29.