I am working w/ a Master spreadsheet, consisting of 80 rows and 20 columns.
The first column has a name. The other columns consist of text, numbers and
dates. My goal is to calculate the time difference between dates if one or
more conditions from the other column(s) are met. The calculation for the
conditions (nested ifs/ands and difference between dates) are no problem.

What I would like to do though is do the calculation on a separate worksheet
if the conditions are met. That way, the new worksheet only has a subset of
the calculations and no blank lines. I don't mind going back to get the
numbers for the calculations using vlookup, I guess I am trying to create the
subset of names that meet the criteria to make it cleaner.

Thanks.

One way

Let's assume your master data is in Sheet1, cols A to C, from row2 down
where col B contains numbers and your criteria is to extract lines over to
Sheet2 where col B 100 (say)

In Sheet2,

Put in A2:
=IF(Sheet1!B2100,ROW(),"")
Leave A1 empty. This is the criteria col.

Put in B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SM ALL($A:$A,ROWS($1:1))))
Copy B2 across by 3 cols to D2. Select A2:D2, copy down to cover the max
expected extent of data in Sheet1. Hide away col A. Cols B to D will return
only the lines from Sheet1 which satisfy the criteria, all neatly bunched at
the top. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PAL" wrote:
I am working w/ a Master spreadsheet, consisting of 80 rows and 20 columns.
The first column has a name. The other columns consist of text, numbers and
dates. My goal is to calculate the time difference between dates if one or
more conditions from the other column(s) are met. The calculation for the
conditions (nested ifs/ands and difference between dates) are no problem.

What I would like to do though is do the calculation on a separate worksheet
if the conditions are met. That way, the new worksheet only has a subset of
the calculations and no blank lines. I don't mind going back to get the
numbers for the calculations using vlookup, I guess I am trying to create the
subset of names that meet the criteria to make it cleaner.

Thanks.

Well Max,

This works great. I have no idea how, but it does. This is a toughie
because I am not familiar with most of the functions. TI will try to learn
them. Thank you.

A question if I may:

The If statement in A2 where you currently have "Sheet1!B2100" could be
more complex and I could use the AND/OR in here if there are multiple
criteria.

"Max" wrote:

One way

Let's assume your master data is in Sheet1, cols A to C, from row2 down
where col B contains numbers and your criteria is to extract lines over to
Sheet2 where col B 100 (say)

In Sheet2,

Put in A2:
=IF(Sheet1!B2100,ROW(),"")
Leave A1 empty. This is the criteria col.

Put in B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SM ALL($A:$A,ROWS($1:1))))
Copy B2 across by 3 cols to D2. Select A2:D2, copy down to cover the max
expected extent of data in Sheet1. Hide away col A. Cols B to D will return
only the lines from Sheet1 which satisfy the criteria, all neatly bunched at
the top. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PAL" wrote:
I am working w/ a Master spreadsheet, consisting of 80 rows and 20 columns.
The first column has a name. The other columns consist of text, numbers and
dates. My goal is to calculate the time difference between dates if one or
more conditions from the other column(s) are met. The calculation for the
conditions (nested ifs/ands and difference between dates) are no problem.

What I would like to do though is do the calculation on a separate worksheet
if the conditions are met. That way, the new worksheet only has a subset of
the calculations and no blank lines. I don't mind going back to get the
numbers for the calculations using vlookup, I guess I am trying to create the
subset of names that meet the criteria to make it cleaner.

Thanks.

Welcome, good to hear that.

The If statement in A2 where you currently
have "Sheet1!B2100" could be more complex and
I could use the AND/OR in here if there are multiple criteria

Yes, of course. Just frame it up to suit your actual criteria to be applied.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PAL" wrote in message
...
Well Max,

This works great. I have no idea how, but it does. This is a toughie
because I am not familiar with most of the functions. TI will try to
learn
them. Thank you.

A question if I may:

The If statement in A2 where you currently have "Sheet1!B2100" could be
more complex and I could use the AND/OR in here if there are multiple
criteria.