Excel Workbook, Data Selection by Date

[Don]

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?

 

[Max]

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

 

[Shane Devenshire]

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.

 

[Don]

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.

 

[Don]

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.

 

[Herbert Seidenberg]

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

 

[Don]

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

 

[Max]

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]

Thanks Max, I will try that.

Don

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