looking for a function that hopefully exist

  • Thread starter Thread starter formula_hardrocker
  • Start date Start date
F

formula_hardrocker

i want to lookup a value in a column on a worksheet and if that value is true
i want to take that information in that row and insert it into another
worksheet but if that value is false i want to just skip that row so i don't
have rows of just zeros across them. thanks
 
Here's a simple, fast, non-array formulas set up to deliver the desired
functionalities
Assume source data in Sheet1, cols A to C, data from row2 down
Assume the key col = col A (names, say)
In another sheet,
A2 will house the input for the name of interest, eg: ABC
In B2: =IF(Sheet1!A2=A$2,ROW(),"")
This is the criteria col. Modify it easily to suit whatever condition you
may have.

In C2:
=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX(Sheet1!A:A,SMALL($B:$B,ROWS($1:1))))
Copy C2 across to E2. Select & copy B2:E2 down to cover the max expected
extent of source data in Sheet1, eg down to E50?. Minimize/hide col B. Cols C
to E will auto-return only the source lines for the name input in A2, all
neatly packed at the top. When you change the name input in A2, it'll return
accordingly (you could have a simple DV droplist for easier selection of
names in A2). Success? hit the YES below.
 
Back
Top