Lookup/Array Formulas

[Guest]

Hi,

I'm trying to create a formula for the following senario...

I have a table of people (by row) who are progressing through the first part
of some training (worksheet 1) and I have a second table (worksheet 2) with
further training for those people who pass the first part.

I want the details of the people who pass to automatically populate the
second sheet but without blank rows, i.e. if numbers 1, 2, 4 & 8 pass from
sheet one (the column J cells show "Yes") I need them to appear in rows 1, 2,
3 & 4 on sheet 2.

Any ideas?...

Thank you!

Jayjay

 

[Guest]

One way to achieve this ..

Assume source data in Sheet1, cols A to J,
data from row2 down with key col = col J

In Sheet2,

Put in A2:
=IF(Sheet1!J2="Yes",ROW(),"")
Leave A1 blank

Put in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW(A1))))
Copy B2 to K2. Select A2:K2, fill down to cover the max expected extent of
source data in Sheet1. Hide away col A. Cols B to K will return the required
results from Sheet1, with all lines neatly bunched at the top.

 

[Guest]

Hi Max,

First of all, thank you - your suggestion has solved a huge part of my
problem.

However, a further problem has now raised it's head...

If, for example, learner number 5 passes their test, they're details are
moved over to the second sheet and further information is recorded in the
columns alongside them.

But, if learner 4 then passes, their details are 'inserted' into the second
sheet above 5, so 5's 'further details' become 4's...

Any thoughts?

Thank you again,

Jayjay

 

[Max]

If you stick to using Sheet1 as the master* to record everything, eg enter
further info in cols K and L (say), then Sheet2 will work to produce a
dynamic filtered list for those who passed. Just extend the formula another
2 cols to bring in cols K and L (Copy B2 to M2. Select A2:M2, fill down).
*you can use autofilter to help search out the record line(s) for input

 

[Guest]

Thanks again, Max, but unfortunately we need to track the training as 2
seperate sheets. i.e. both will be 'masters' for the training they are
tracking.

Any further ideas?...

Jay

 

[Max]

No, afraid not. Think you'd need vba. Hang around awhile for insights from
others.