BETTER SPACING - If Then Date Range

[Mark B]

I want to pull in to Sheet 1 a list of names from another sheet based upon a
date range in the second sheet...

In my example, the reason Fred is in ColA on Sheet 1 is because his date
range in Sheet 2 matches this formula... ColB<=01-21-06 AND ColC >=01-21-06

Sheet 1 Sheet 2
ColA ColB ColA ColB ColC
1-21-06 1-28-06 Fred 1-19-06 1-24-06
Fred Lucy Ethel 1-19-06 1-24-06
Ethel Ricky Lucy 1-26-06 1-30-06
Ricky 1-26-06 1-30-06

 

[bpeltzer]

I don't think you're going to to this strictly with formulas. A macro could
do it, or you can do it with filters. Highlight your table, then on the menu
bar Data > Filter > Autofilter. Use the drop-down in col B to select Custom,
then use the dialog to choose <= 1/21/06. Use the drop-down in col C to
select Custom, then use the dialog to choose >=1-21-06. All the rows now
visible should meet your criteria. So select then all, copy and paste to
sheet1.

 

[Mark B]

Thanks for the reply. The problem is I need this to update everytime a user
opens the file. It's not just me who will be looking at the data.

So there is no "easy" way to say this...?

=IF(Sheet2!B2<=01-21-06 AND Sheet2!B3>=01-21-06,Sheet2!B1,0)

 

[vezerid]

Mark,
although I might try to come up with a purely formula-based solution,
(which will likely be complex),
there is a way to automate this process with event macros. The macro
can be called automatically every time the workbook is opened, with the
workbook_open() event macro. So the macro can be written once, callable
from a button, and also from the workbook_open() macro. I think this is
the most efficient solution.

Kostis Vezerides

 

[bpeltzer]

Well 'saying that' is easy enough:
=if(and( Sheet2!B2 <= date (2006,1,21), Sheet2!B3 >=
date(2006,1,21)),Sheet2!B1,0).
And if you're okay with having a bunch of blank rows on Sheet1 where the
corresponding dates in Sheet2 did not meet the condition, then that construct
would work fine. But if you want to selectively copy the rows from Sheet2
that meet your condition, then I'd agree with the macro approach that Kostis
suggests.

 

[Mark B]

Well, that was EXACTLY the formula I was looking for and you are EXACTLY
correct, it is not going to do what I want it to do. Darn

I hate macro's, and I wish I could understand even half of the post you
wrote below

 

[Mark B]

I am half way there. Maybe you can get me the rest of the way. If I wasn't
detailed enough in my original posts with what I am trying to accomplish,
please forgive me

I have a workbook setup with 8 sheets, numbered 1 through 8

Sheet 1 is where I want to pull all the data into.

Sheets 2 through 8 are all pulling in the same data from an external source,
and each sheet is being AutoFiltered (Custom) with a different set of
criteria.

I now want to pull the A Column of each Sheet's filtered data into a
seperate column in Sheet 1, but I cannot figure out how to do it so that it
only pulls filtered data.

Any help is MUCH appreciated!!!

Thanks! MB

 

[vezerid]

So, Mark, the original problem that you posted does not exactly reflect
your actual setup. What you posted is more like an effort for a digest,
where you envisioned putting all your criteria together in a 9th sheet.
Your problem is basically the following: you want to start with a
source list and then produce a destination list, which is a subset of
the first, according to arbitrary criteria, and you want the subset to
be contiguous. In this case the source list has a fixed number of
columns (as shown in your original post as Sheet2) and you want to
produce various destination lists in (necessarily?) adjacent columns.

So, I will give you a *sample* solution, formula based, which can be
adapted to suit your needs. This is based on your approach of the
original post, namely that you somehow parametrize your criteria
instead of trying to write a macro which will pull of the filtered data
only.

The following example assumes a list of numbers from 0 to 1000 in cells
A1:A20. We want to produce the subset of this list that contains
numbers less than 500. I will use two auxiliary columns: Column D
will compute the row numbers of where admissible numbers are and column
E:E will produce the actual data number. In both cases the destination
lists start from row 1.

Formulas in column D are to be array-entered (Shift+Ctrl+Enter).

In D1:
=MIN(ROW(A1:A20)*(IF(A1:A20<500, 1, 10000)))
In D2:
=MIN(ROW($A$1:$A$20)*(IF($A$1:$A$20<500, 1,
10000))*IF(ROW($A$1:$A$20)>D1,1,10000))
Copy D2 down until a very large row number appears.

In E1:
=IF(D1<$D$1*10000, OFFSET($A$1,D1-1,0), "")
Copy down as necessary.

The condition is in the IF(A1:A20<500, 1, 10000) in D1 and
IF($A$1:$A$20<500, 1, 10000) in D2. You can modify the condition to
suit your needs.

Drawbacks:
- You must have enough copies of formulas down, which might make the
spreadsheet heavy in recalculation
- You need two columns per destination column. But these you can hide.

The OFFSET formula is enclosed in an IF() so as to not display 0's. The
criterion for this IF is explained as follows: $D$1 will have the first
row where data is found. This row number times 10000 is the large
number that will appear in column D after you run out of data.

Write back if you need more.

HTH
Kostis Vezerides