On one tab of my spreadsheet, I have the following data in Columns
Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest

On a seperate tab of the spreadsheet, I'd like to have my data automatically
populated from the first tab based on the Purch Date.

For example, if I have 5 lines of data on tab one...and three of the lines
have the same purch date, I'd like excel to automatically list this data on
tab two once I change a date on tab two.

I've tried using Index and Lookup formulas, but they will only list the
first line item that matches this Purch Date...not the subsequent data.

How can I have these other lines of data extracted to the 2nd tab without
duplicating lines...and without omitting lines?

One way using non-array formulas to achieve it dynamically ..

Source data is assumed in sheet: X,
from row2 down in cols A to E, with col A = key col (Purchase Date)

In another sheet: Y (say),

Paste the same col headers into A1:E1
Let's say G1 will house the input of the purchase date (of interest)

Put in A2:
=IF(ROW(A1)COUNT($F:$F),"",INDEX(X!A:A,MATCH(SMAL L($F:$F,ROW(A1)),$F:$F,0)))
Copy A2 to E2

Put in F2:
=IF(X!A2="","",IF(X!A2=$G$1,ROW(),""))
(Leave F1 empty)

Then just select A2:F2 and copy down to cover the max expected extent of
data in X. Format cols A & B as dates. Hide away the criteria col F if
desired. Cols A to F will return the required results dynamically from X, ie
only the lines with purchase dates equal to that input in G1. All results
will be neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote:
On one tab of my spreadsheet, I have the following data in Columns
Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest

On a seperate tab of the spreadsheet, I'd like to have my data automatically
populated from the first tab based on the Purch Date.

For example, if I have 5 lines of data on tab one...and three of the lines
have the same purch date, I'd like excel to automatically list this data on
tab two once I change a date on tab two.

I've tried using Index and Lookup formulas, but they will only list the
first line item that matches this Purch Date...not the subsequent data.

How can I have these other lines of data extracted to the 2nd tab without
duplicating lines...and without omitting lines?

Max....
I think I figured out what the problem was....
Will this formula only work if the column headers are in row 1?
When I delete all the other header information....everything works.
Can I not have the same thing work if my column headers start on row 7 for
both sheets?
Thanks again

"Brian" wrote:

Max,
Thanks very much for your assistance....I think this is exactly what I
need...but for some reason I still can't get it to work correctly.
I did a test using the example you listed below....but when I try the same
thing with the actual spreadsheet, my data field come up blank.
The only revisions I made to your example below is the following:
My Column Headers run from column A7 to Column J7.
I then have column K represent column F in your example
And instead of putting my date in G1...I have it in C3 instead.

My formulas in column a thru J a
=IF(ROW(A7)COUNT($K:$K),"",INDEX(Investments!A:A, MATCH(SMALL($K:$K,ROW(A7)),$K:$K,0))) ....The A's change based on the column this formula is in.

My formula in column K is
=IF(Investments!A7="","",IF(Investments!A7=$C$3,RO W(),""))

Any idea why this still isn't working?

Thanks again...I really appreciate it!!!

"Max" wrote:

One way using non-array formulas to achieve it dynamically ..

Source data is assumed in sheet: X,
from row2 down in cols A to E, with col A = key col (Purchase Date)

In another sheet: Y (say),

Paste the same col headers into A1:E1
Let's say G1 will house the input of the purchase date (of interest)

Put in A2:
=IF(ROW(A1)COUNT($F:$F),"",INDEX(X!A:A,MATCH(SMAL L($F:$F,ROW(A1)),$F:$F,0)))
Copy A2 to E2

Put in F2:
=IF(X!A2="","",IF(X!A2=$G$1,ROW(),""))
(Leave F1 empty)

Then just select A2:F2 and copy down to cover the max expected extent of
data in X. Format cols A & B as dates. Hide away the criteria col F if
desired. Cols A to F will return the required results dynamically from X, ie
only the lines with purchase dates equal to that input in G1. All results
will be neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote:
On one tab of my spreadsheet, I have the following data in Columns
Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest

On a seperate tab of the spreadsheet, I'd like to have my data automatically
populated from the first tab based on the Purch Date.

For example, if I have 5 lines of data on tab one...and three of the lines
have the same purch date, I'd like excel to automatically list this data on
tab two once I change a date on tab two.

I've tried using Index and Lookup formulas, but they will only list the
first line item that matches this Purch Date...not the subsequent data.

How can I have these other lines of data extracted to the 2nd tab without
duplicating lines...and without omitting lines?

Max,
Thanks very much for your assistance....I think this is exactly what I
need...but for some reason I still can't get it to work correctly.
I did a test using the example you listed below....but when I try the same
thing with the actual spreadsheet, my data field come up blank.
The only revisions I made to your example below is the following:
My Column Headers run from column A7 to Column J7.
I then have column K represent column F in your example
And instead of putting my date in G1...I have it in C3 instead.

My formulas in column a thru J a
=IF(ROW(A7)COUNT($K:$K),"",INDEX(Investments!A:A, MATCH(SMALL($K:$K,ROW(A7)),$K:$K,0))) ....The A's change based on the column this formula is in.

My formula in column K is
=IF(Investments!A7="","",IF(Investments!A7=$C$3,RO W(),""))

Any idea why this still isn't working?

Thanks again...I really appreciate it!!!

"Max" wrote:

One way using non-array formulas to achieve it dynamically ..

Source data is assumed in sheet: X,
from row2 down in cols A to E, with col A = key col (Purchase Date)

In another sheet: Y (say),

Paste the same col headers into A1:E1
Let's say G1 will house the input of the purchase date (of interest)

Put in A2:
=IF(ROW(A1)COUNT($F:$F),"",INDEX(X!A:A,MATCH(SMAL L($F:$F,ROW(A1)),$F:$F,0)))
Copy A2 to E2

Put in F2:
=IF(X!A2="","",IF(X!A2=$G$1,ROW(),""))
(Leave F1 empty)

Then just select A2:F2 and copy down to cover the max expected extent of
data in X. Format cols A & B as dates. Hide away the criteria col F if
desired. Cols A to F will return the required results dynamically from X, ie
only the lines with purchase dates equal to that input in G1. All results
will be neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote:
On one tab of my spreadsheet, I have the following data in Columns
Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest

On a seperate tab of the spreadsheet, I'd like to have my data automatically
populated from the first tab based on the Purch Date.

For example, if I have 5 lines of data on tab one...and three of the lines
have the same purch date, I'd like excel to automatically list this data on
tab two once I change a date on tab two.

I've tried using Index and Lookup formulas, but they will only list the
first line item that matches this Purch Date...not the subsequent data.

How can I have these other lines of data extracted to the 2nd tab without
duplicating lines...and without omitting lines?

Can I not have the same thing work
if my column headers start on row 7 for both sheets?

Presume you mean if source data starts in row7 (instead of in row2). Yes, of
course it can still work but we'd need to make an arithmetic adjustment in
the extract formula.

Here's a working sample configured to suit for easy reference:
http://cjoint.com/?iqiAC6FnX8
Filtering from another sht.xls

Source sheet is named: Investments,
data in cols A to J, headers in A6:J6, data from row7 down

In a new sheet, Y (say)

[Purch] Date of interest will be input in L1
(or the date could be selected from a DV in L1, say)

In A2:
=IF(ROW(A1)COUNT($K:$K),"",
INDEX(Investments!A:A,MATCH(SMALL($K:$K,ROW(A1)),$ K:$K,0)+5))
Copy A2 to J2

[The "+5" is an arithmetic adjustment as source data starts in row7, and we
are pulling it from row2 here in Y. ROW(A1) is always used in the starting
formula cell, irrespective of where this start cell may be. ROW(A1) is used
as an incrementer in the formula.]

In K2:
=IF(Investments!A7="","",IF(Investments!A7=$L$1,RO W(),""))

Select A2:K2, fill down to cover the max expected extent of data in
Investments.
Format cols A & B as dates
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote:
Max....
I think I figured out what the problem was....
Will this formula only work if the column headers are in row 1?
When I delete all the other header information....everything works.
Can I not have the same thing work if my column headers start on row 7 for
both sheets?

Here's a more enduring <g link to the working sample:
http://savefile.com/files/5594819
Filtering from another sht.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,
Thanks very much for your assistance....
For some reason I still can't get this to work correctly.
I've done everything you suggested...and it works perfectly with a test
file....but when I try to apply the formulas to my actual spreadsheet, the
data keeps coming up blank.
I can't seem to figure out what I'm doing wrong.
I'll continue to play around with it...because I know it should work and
your suggestion is exactly what I need.

Thanks again

"Max" wrote:

Here's a more enduring <g link to the working sample:
http://savefile.com/files/5594819
Filtering from another sht.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Perhaps the source purchase "dates" in Investments' col A aren't real dates
recognized by Excel.

Try this on a spare copy of your actual data ..

Select only the "dates" range in Investments' col A
Click Data Text To Columns
Click Next Next to proceed to step 3 of the wizard
In step 3, under "Col data format", check "Date",
then select the appropriate date format from the droplist, eg: MDY
Click Finish

The above should convert all the dates to real dates recognized by Excel,
and everything should work fine now.

Alternatively, we could also give this a try ..

Put instead in K2, and fill down:
=IF(Investments!A7="","",IF(Investments!A7+0=$L$1, ROW(),""))

The "+0" may suffice to coerce the text dates in Investments' col A to real
dates
Let me know how it goes ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote in message
...
Max,
Thanks very much for your assistance....
For some reason I still can't get this to work correctly.
I've done everything you suggested...and it works perfectly with a test
file....but when I try to apply the formulas to my actual spreadsheet, the
data keeps coming up blank.
I can't seem to figure out what I'm doing wrong.
I'll continue to play around with it...because I know it should work and
your suggestion is exactly what I need.

Thanks again

"Max" wrote:

Here's a more enduring <g link to the working sample:
http://savefile.com/files/5594819
Filtering from another sht.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Max" wrote:

Can I not have the same thing work
if my column headers start on row 7 for both sheets?

Presume you mean if source data starts in row7 (instead of in row2). Yes,
of
course it can still work but we'd need to make an arithmetic adjustment in
the extract formula.

Here's a working sample configured to suit for easy reference:
http://cjoint.com/?iqiAC6FnX8
Filtering from another sht.xls

Source sheet is named: Investments,
data in cols A to J, headers in A6:J6, data from row7 down

In a new sheet, Y (say)

[Purch] Date of interest will be input in L1
(or the date could be selected from a DV in L1, say)

In A2:
=IF(ROW(A1)COUNT($K:$K),"",
INDEX(Investments!A:A,MATCH(SMALL($K:$K,ROW(A1)),$ K:$K,0)+5))
Copy A2 to J2

[The "+5" is an arithmetic adjustment as source data starts in row7, and
we
are pulling it from row2 here in Y. ROW(A1) is always used in the starting
formula cell, irrespective of where this start cell may be. ROW(A1) is
used
as an incrementer in the formula.]

In K2:
=IF(Investments!A7="","",IF(Investments!A7=$L$1,RO W(),""))

Select A2:K2, fill down to cover the max expected extent of data in
Investments.
Format cols A & B as dates
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote:
Max....
I think I figured out what the problem was....
Will this formula only work if the column headers are in row 1?
When I delete all the other header information....everything works.
Can I not have the same thing work if my column headers start on row 7
for
both sheets?

That did it Max!
I used your suggestion and now everything has worked perfectly!!!
Thanks again for your help!!!! I never would've been able to do it without
you!

"Max" wrote:

Perhaps the source purchase "dates" in Investments' col A aren't real dates
recognized by Excel.

Try this on a spare copy of your actual data ..

Select only the "dates" range in Investments' col A
Click Data Text To Columns
Click Next Next to proceed to step 3 of the wizard
In step 3, under "Col data format", check "Date",
then select the appropriate date format from the droplist, eg: MDY
Click Finish

The above should convert all the dates to real dates recognized by Excel,
and everything should work fine now.

Alternatively, we could also give this a try ..

Put instead in K2, and fill down:
=IF(Investments!A7="","",IF(Investments!A7+0=$L$1, ROW(),""))

The "+0" may suffice to coerce the text dates in Investments' col A to real
dates
Let me know how it goes ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote in message
...
Max,
Thanks very much for your assistance....
For some reason I still can't get this to work correctly.
I've done everything you suggested...and it works perfectly with a test
file....but when I try to apply the formulas to my actual spreadsheet, the
data keeps coming up blank.
I can't seem to figure out what I'm doing wrong.
I'll continue to play around with it...because I know it should work and
your suggestion is exactly what I need.

Thanks again

"Max" wrote:

Here's a more enduring <g link to the working sample:
http://savefile.com/files/5594819
Filtering from another sht.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Max" wrote:

Can I not have the same thing work
if my column headers start on row 7 for both sheets?

Presume you mean if source data starts in row7 (instead of in row2). Yes,
of
course it can still work but we'd need to make an arithmetic adjustment in
the extract formula.

Here's a working sample configured to suit for easy reference:
http://cjoint.com/?iqiAC6FnX8
Filtering from another sht.xls

Source sheet is named: Investments,
data in cols A to J, headers in A6:J6, data from row7 down

In a new sheet, Y (say)

[Purch] Date of interest will be input in L1
(or the date could be selected from a DV in L1, say)

In A2:
=IF(ROW(A1)COUNT($K:$K),"",
INDEX(Investments!A:A,MATCH(SMALL($K:$K,ROW(A1)),$ K:$K,0)+5))
Copy A2 to J2

[The "+5" is an arithmetic adjustment as source data starts in row7, and
we
are pulling it from row2 here in Y. ROW(A1) is always used in the starting
formula cell, irrespective of where this start cell may be. ROW(A1) is
used
as an incrementer in the formula.]

In K2:
=IF(Investments!A7="","",IF(Investments!A7=$L$1,RO W(),""))

Select A2:K2, fill down to cover the max expected extent of data in
Investments.
Format cols A & B as dates
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote:
Max....
I think I figured out what the problem was....
Will this formula only work if the column headers are in row 1?
When I delete all the other header information....everything works.
Can I not have the same thing work if my column headers start on row 7
for
both sheets?

Delighted to hear that, Brian !
Thanks for calling home ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote:
That did it Max!
I used your suggestion and now everything has worked perfectly!!!
Thanks again for your help!!!! I never would've been able to do it without
you!

Max,

I have been reading this thread with interest as it goes 90% of the way to
solving a problem I have.

Is there a way of amending your formula to return data between two dates,
i.e. as per Brian's example, could we return entries between 22 Jan 07 and 29
Jan 07 based on purchdate?

I very much look forward to hearing from you.

Kind regards,

Paul

"Max" wrote:

Delighted to hear that, Brian !
Thanks for calling home ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote:
That did it Max!
I used your suggestion and now everything has worked perfectly!!!
Thanks again for your help!!!! I never would've been able to do it without
you!

.. to return data between two dates ..

Sure, we just need to amend the earlier criteria formula in col K

Instead of:
In K2, and fill down:
=IF(Investments!A7="","",IF(Investments!A7+0=$L$1, ROW(),""))

Assuming the (purchase) startdate will be input in L1,
with enddate input in L2

Use this amended criteria in K2, and fill down:
=IF(OR(Investments!A7="",$L$1="",$L$2=""),"",IF(AN D(Investments!A7+0=$L$1,Investments!A7+0<=$L$2),R OW(),""))

No change to the rest of the set-up.
It should work just as well as before <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lancer940" wrote:
Max,

I have been reading this thread with interest as it goes 90% of the way to
solving a problem I have.

Is there a way of amending your formula to return data between two dates,
i.e. as per Brian's example, could we return entries between 22 Jan 07 and 29
Jan 07 based on purchdate?

I very much look forward to hearing from you.

Kind regards,

Paul

Hi Max,

Thanks very much for the prompt reply.

Unfortunately I am having some problems.

My formula looks like this:
=IF(ROW(B3)COUNT($S:$S),"",INDEX(data!A:A,MATCH(S MALL($S:$S,ROW(B3)),$S:$S,0)))

My problem is that the array "data" does not seem to be picked up in the
formula -Excel starts looking for it in another workbook. When I add the "!"
in the formula bar the array just becomes text.

Also, I assume that "data" should be an array on the source data sheet. Is
that right?

Paul

"Max" wrote:

.. to return data between two dates ..

Sure, we just need to amend the earlier criteria formula in col K

Instead of:
In K2, and fill down:
=IF(Investments!A7="","",IF(Investments!A7+0=$L$1, ROW(),""))

Assuming the (purchase) startdate will be input in L1,
with enddate input in L2

Use this amended criteria in K2, and fill down:
=IF(OR(Investments!A7="",$L$1="",$L$2=""),"",IF(AN D(Investments!A7+0=$L$1,Investments!A7+0<=$L$2),R OW(),""))

No change to the rest of the set-up.
It should work just as well as before <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lancer940" wrote:
Max,

I have been reading this thread with interest as it goes 90% of the way to
solving a problem I have.

Is there a way of amending your formula to return data between two dates,
i.e. as per Brian's example, could we return entries between 22 Jan 07 and 29
Jan 07 based on purchdate?

I very much look forward to hearing from you.

Kind regards,

Paul

Max,

Final update. Got everything to work just fine. Thanks to your previous
post.

The formula in cell A3 of the output worksheet is ('DT Stats' is the name of
the source data worksheet):

=IF(ROW(A1)COUNT($S:$S),"",INDEX('DT
Stats'!A:A,MATCH(SMALL($S:$S,ROW(A1)),$S:$S,0)))

In cell S3:

=IF(OR('DT Stats'!B3="",$B$1="",$D$1=""),"",IF(AND('DT Stats'!B3+0=$B$1,'DT
Stats'!B3+0<=$D$1),ROW(),""))

The data in both worksheets starts from Row 3. The date from the source
data ('DT Stats') is in Col B. The from/to dates are in cells B1 and D1.

Thank you very much for taking the time to help me out.

Kind regards,

Paul

In

"Lancer940" wrote:

Hi Max,

Thanks very much for the prompt reply.

Unfortunately I am having some problems.

My formula looks like this:
=IF(ROW(B3)COUNT($S:$S),"",INDEX(data!A:A,MATCH(S MALL($S:$S,ROW(B3)),$S:$S,0)))

My problem is that the array "data" does not seem to be picked up in the
formula -Excel starts looking for it in another workbook. When I add the "!"
in the formula bar the array just becomes text.

Also, I assume that "data" should be an array on the source data sheet. Is
that right?

Paul

"Max" wrote:

.. to return data between two dates ..

Sure, we just need to amend the earlier criteria formula in col K

Instead of:
In K2, and fill down:
=IF(Investments!A7="","",IF(Investments!A7+0=$L$1, ROW(),""))

Assuming the (purchase) startdate will be input in L1,
with enddate input in L2

Use this amended criteria in K2, and fill down:
=IF(OR(Investments!A7="",$L$1="",$L$2=""),"",IF(AN D(Investments!A7+0=$L$1,Investments!A7+0<=$L$2),R OW(),""))

No change to the rest of the set-up.
It should work just as well as before <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lancer940" wrote:
Max,

I have been reading this thread with interest as it goes 90% of the way to
solving a problem I have.

Is there a way of amending your formula to return data between two dates,
i.e. as per Brian's example, could we return entries between 22 Jan 07 and 29
Jan 07 based on purchdate?

I very much look forward to hearing from you.

Kind regards,

Paul

.. Got everything to work just fine.

Glad to hear that, Paul !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Jan 25, 1:55 am, Lancer940
wrote:
Max,

Final update. Got everything to work just fine. Thanks to your previous
post.

The formula in cell A3 of the output worksheet is ('DT Stats' is the name of
the source data worksheet):

=IF(ROW(A1)COUNT($S:$S),"",INDEX('DT
Stats'!A:A,MATCH(SMALL($S:$S,ROW(A1)),$S:$S,0)))

In cell S3:

=IF(OR('DT Stats'!B3="",$B$1="",$D$1=""),"",IF(AND('DT Stats'!B3+0=$B$1,'DT
Stats'!B3+0<=$D$1),ROW(),""))

The data in both worksheets starts from Row 3. The date from the source
data ('DT Stats') is in Col B. The from/to dates are in cells B1 and D1.

Thank you very much for taking the time to help me out.
Kind regards,
Paul