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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jayjay" wrote:
Hi,

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

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

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" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jayjay" wrote:
Hi,

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

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jayjay" wrote in message
...
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

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" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jayjay" wrote in message
...
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

No, afraid not. Think you'd need vba. Hang around awhile for insights from
others.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jayjay" wrote in message
...
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