Excel Workbook, Data Selection by Date
 

I am developing an excel workbook to track retirement funds. Each worksheet
represents a separate fund. The last worksheet contains a summary of the
various funds including latest unit price, number of units, etc. From each
worksheet (fund) I want the workbook to find the most recent entry (from the
date column) & corresponding unit price (from the price column on the same
row) & transfer this information to assigned cells in the summary sheet.
What function/programming is required to make this work?

Excel Workbook, Data Selection by Date
 

One thought which assumes that the most recent date & corresponding price in
the fund sheets will always be the data that's in the last filled row
(chronologically filled down)

For example, in the respective fund sheets (all sheets identically
structured), assume you have this kind of set up, dates in col A, prices in
col B, data in row2 down

In: Fund1
Date Price
1-Dec-08 10
1-Jan-09 20
etc

In: Fund2
Date Price
15-Dec-08 80
3-Jan-09 70
4-Jan-09 71
etc

Then in your Summary sheet, you could set it up like this
"Fund" sheetnames listed in B1 across, viz: Fund1, Fund2, ...
Labels placed in A2:A3: Most recent date, Price

Put in B1:
=LOOKUP(2,1/(INDIRECT("'"&B$1&"'!A2:A100")<""),INDIRECT("'"&B $1&"'!A2:A100"))

Put in B2:
=LOOKUP(2,1/(INDIRECT("'"&B$1&"'!A2:A100")<""),INDIRECT("'"&B $1&"'!B2:B100"))

Copy B1:B2 across as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Don" wrote:
I am developing an excel workbook to track retirement funds. Each worksheet
represents a separate fund. The last worksheet contains a summary of the
various funds including latest unit price, number of units, etc. From each
worksheet (fund) I want the workbook to find the most recent entry (from the
date column) & corresponding unit price (from the price column on the same
row) & transfer this information to assigned cells in the summary sheet.
What function/programming is required to make this work?

Excel Workbook, Data Selection by Date
 

Hi,

Try something like this

=LOOKUP(9^9,Sheet2!$A$1:$A$99,$B$1:$B$99)

This assumes the dates are in column A and in ascending order with the
values in column B.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Don" wrote:

I am developing an excel workbook to track retirement funds. Each worksheet
represents a separate fund. The last worksheet contains a summary of the
various funds including latest unit price, number of units, etc. From each
worksheet (fund) I want the workbook to find the most recent entry (from the
date column) & corresponding unit price (from the price column on the same
row) & transfer this information to assigned cells in the summary sheet.
What function/programming is required to make this work?

Excel Workbook, Data Selection by Date
 

Thanks Max .... but still having trouble.

Perhaps a bit more discription will help. The fund sheets are similar to
your example (Column A is for dates), however, the unit prices are in Column
C. As for the summary sheet, Column A contains the names of all the funds
(worksheets) & Column C is reserved for the unit prices which correspond to
the most recent entries (dates). The dates are not shown on the summary
sheet.

"Max" wrote:

One thought which assumes that the most recent date & corresponding price in
the fund sheets will always be the data that's in the last filled row
(chronologically filled down)

For example, in the respective fund sheets (all sheets identically
structured), assume you have this kind of set up, dates in col A, prices in
col B, data in row2 down

In: Fund1
Date Price
1-Dec-08 10
1-Jan-09 20
etc

In: Fund2
Date Price
15-Dec-08 80
3-Jan-09 70
4-Jan-09 71
etc

Then in your Summary sheet, you could set it up like this
"Fund" sheetnames listed in B1 across, viz: Fund1, Fund2, ...
Labels placed in A2:A3: Most recent date, Price

Put in B1:
=LOOKUP(2,1/(INDIRECT("'"&B$1&"'!A2:A100")<""),INDIRECT("'"&B $1&"'!A2:A100"))

Put in B2:
=LOOKUP(2,1/(INDIRECT("'"&B$1&"'!A2:A100")<""),INDIRECT("'"&B $1&"'!B2:B100"))

Copy B1:B2 across as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Don" wrote:
I am developing an excel workbook to track retirement funds. Each worksheet
represents a separate fund. The last worksheet contains a summary of the
various funds including latest unit price, number of units, etc. From each
worksheet (fund) I want the workbook to find the most recent entry (from the
date column) & corresponding unit price (from the price column on the same
row) & transfer this information to assigned cells in the summary sheet.
What function/programming is required to make this work?

Excel Workbook, Data Selection by Date
 

Thanks Shane, but I am getting a $0.00 value. Perhaps the following
additional description will help. The fund worksheets have the dates in
column A & the corresponding unit prices in column C. The summary sheet has
the names of the funds (worksheets) in column A & the most recent unit price
for each in column C.

"Shane Devenshire" wrote:

Hi,

Try something like this

=LOOKUP(9^9,Sheet2!$A$1:$A$99,$B$1:$B$99)

This assumes the dates are in column A and in ascending order with the
values in column B.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Don" wrote:

I am developing an excel workbook to track retirement funds. Each worksheet
represents a separate fund. The last worksheet contains a summary of the
various funds including latest unit price, number of units, etc. From each
worksheet (fund) I want the workbook to find the most recent entry (from the
date column) & corresponding unit price (from the price column on the same
row) & transfer this information to assigned cells in the summary sheet.
What function/programming is required to make this work?

Excel Workbook, Data Selection by Date
 

Excel 2007
Here is a general example of a PivotTable
consolidating several tabs
and filtering the date results by some criteria.
Since PivotTable has lots of flexibility,
I am sure you can fit it to your layout.
http://www.mediafire.com/file/anwntty43wt/01_25_09.xlsm

Excel Workbook, Data Selection by Date
 

Thank you Herbert, however, I am using excel 2003.

"Herbert Seidenberg" wrote:

Excel 2007
Here is a general example of a PivotTable
consolidating several tabs
and filtering the date results by some criteria.
Since PivotTable has lots of flexibility,
I am sure you can fit it to your layout.
http://www.mediafire.com/file/anwntty43wt/01_25_09.xlsm

Excel Workbook, Data Selection by Date
 

In your Summary,
with the "fund" sheetnames* listed in A2 down
Try this in B2:
=LOOKUP(2,1/(INDIRECT("'"&$A2&"'!A2:A100")<""),INDIRECT("'"&$ A2&"'!C2:C100"))
Copy down to return the latest** prices from col C in each fund's sheet.
Adapt the ranges to suit the largest expected extent of data.

*Ensure that the sheetnames listed in A2 down match exactly (except for
case) with what's on the tabs. Watch for typos, extra spaces which might
throw matching off
**Latest prices are assumed the data in col C corresponding to the
"bottomest" date in col A
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Don" wrote:
Thanks Max .... but still having trouble.
Perhaps a bit more discription will help. The fund sheets are similar to
your example (Column A is for dates), however, the unit prices are in Column
C. As for the summary sheet, Column A contains the names of all the funds
(worksheets) & Column C is reserved for the unit prices which correspond to
the most recent entries (dates). The dates are not shown on the summary
sheet.

Excel Workbook, Data Selection by Date
 

Thanks Max, I will try that.

Don

"Max" wrote:

In your Summary,
with the "fund" sheetnames* listed in A2 down
Try this in B2:
=LOOKUP(2,1/(INDIRECT("'"&$A2&"'!A2:A100")<""),INDIRECT("'"&$ A2&"'!C2:C100"))
Copy down to return the latest** prices from col C in each fund's sheet.
Adapt the ranges to suit the largest expected extent of data.

*Ensure that the sheetnames listed in A2 down match exactly (except for
case) with what's on the tabs. Watch for typos, extra spaces which might
throw matching off
**Latest prices are assumed the data in col C corresponding to the
"bottomest" date in col A
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Don" wrote:
Thanks Max .... but still having trouble.
Perhaps a bit more discription will help. The fund sheets are similar to
your example (Column A is for dates), however, the unit prices are in Column
C. As for the summary sheet, Column A contains the names of all the funds
(worksheets) & Column C is reserved for the unit prices which correspond to
the most recent entries (dates). The dates are not shown on the summary
sheet.