I am keeping a running log of mutual fund activity. Something like this:

Date Shares Price Daily Total Gain/Loss
6/1/2007 503.30 40.53 20,398.83 0.00
6/2/2007 503.30 40.59 0.06 20,429.03 30.20


I have several funds like this. I want to take the most recent balances and
summarize them on another sheet. So in other words, when i enter in a new
days worth of infomation, the formula on the other sheet will automatically
pick up the latest information. Something like this:

Fund A Shares Balance
Fund B Shares Balance

Any way to do this easily? Also, would there be an easy way to maybe drop in
a date and have it pick up the information for that date? I am trying to
create Quicken or Money in Excel because those 2 programs can be SOOOOO hard
to use for this stuff. Thanks.

I assume you have a separate sheet for each fund.

In your master sheet compile, in column A:A, the names of all the
worksheets containing funds. Say you start from A2 to allow for
headers. B2 will contain the shares and C2 the balance. In B2, C2:

=LOOKUP(10^307,INDIRECT("'"&A2&"'!B:B"))
=LOOKUP(10^307,INDIRECT("'"&A2&"'!E:E"))

For the last one I am not sure which column you consider the be the
balance, but change the column in the formula.

HTH
Kostis Vezerides

On Jun 7, 6:58 am, Jon Dow wrote:
I am keeping a running log of mutual fund activity. Something like this:

Date Shares Price Daily Total Gain/Loss
6/1/2007 503.30 40.53 20,398.83 0.00
6/2/2007 503.30 40.59 0.06 20,429.03 30.20

I have several funds like this. I want to take the most recent balances and
summarize them on another sheet. So in other words, when i enter in a new
days worth of infomation, the formula on the other sheet will automatically
pick up the latest information. Something like this:

Fund A Shares Balance
Fund B Shares Balance

Any way to do this easily? Also, would there be an easy way to maybe drop in
a date and have it pick up the information for that date? I am trying to
create Quicken or Money in Excel because those 2 programs can be SOOOOO hard
to use for this stuff. Thanks.

I did not understand this formula nor could I get it to work. Please explain
further. To recap, i have this in A3:d7

Date Shares Price Value
6/1/2007 400.11 34.56 13827.8016
6/2/2007 400.11 34.59 13839.8049
6/3/2007 400.11 34.06 13627.7466
6/4/2007 400.11 35.09 14039.8599

I have 10 sheets like this. I want to have a total sheet with all 10
accounts listed. I want the total line for each to show the most recent
balances. So it will show 6-4 balance but when i add the 6-5 numbers the
total line will show that line since it is the most recent. Any thoughts?

"vezerid" wrote:

I assume you have a separate sheet for each fund.

In your master sheet compile, in column A:A, the names of all the
worksheets containing funds. Say you start from A2 to allow for
headers. B2 will contain the shares and C2 the balance. In B2, C2:

=LOOKUP(10^307,INDIRECT("'"&A2&"'!B:B"))
=LOOKUP(10^307,INDIRECT("'"&A2&"'!E:E"))

For the last one I am not sure which column you consider the be the
balance, but change the column in the formula.

HTH
Kostis Vezerides

On Jun 7, 6:58 am, Jon Dow wrote:
I am keeping a running log of mutual fund activity. Something like this:

Date Shares Price Daily Total Gain/Loss
6/1/2007 503.30 40.53 20,398.83 0.00
6/2/2007 503.30 40.59 0.06 20,429.03 30.20

I have several funds like this. I want to take the most recent balances and
summarize them on another sheet. So in other words, when i enter in a new
days worth of infomation, the formula on the other sheet will automatically
pick up the latest information. Something like this:

Fund A Shares Balance
Fund B Shares Balance

Any way to do this easily? Also, would there be an easy way to maybe drop in
a date and have it pick up the information for that date? I am trying to
create Quicken or Money in Excel because those 2 programs can be SOOOOO hard
to use for this stuff. Thanks.

The formula:
=LOOKUP(10^307,A:A)

will find the last (location wise) cell with any number in A:A. This
is an intentional misuse of LOOKUP that has this desirable effect.

INDIRECT builds cell references dynamically. Sort of like, instead of
necessarily looking up A:A, it can look at any range that is specified
by a string, built dynamically.

If your sheet is called "John Dow Fund" and cell A2 contains exactly
the sheet's name then the two are "equivalent"

=LOOKUP(10^307,INDIRECT("'"&A2&"'!B:B"))
=LOOKUP(10^307,'John Dow Fund'!B:B)

If a new date is added below the data in John Dow Fund'!B:B then the
formula will show the last value.

Do you have in the aux column the exact names of the sheets?

HTH
Kostis Vezerides

On Jun 7, 11:57 pm, Jon Dow wrote:
I did not understand this formula nor could I get it to work. Please explain
further. To recap, i have this in A3:d7

Date Shares Price Value
6/1/2007 400.11 34.56 13827.8016
6/2/2007 400.11 34.59 13839.8049
6/3/2007 400.11 34.06 13627.7466
6/4/2007 400.11 35.09 14039.8599

I have 10 sheets like this. I want to have a total sheet with all 10
accounts listed. I want the total line for each to show the most recent
balances. So it will show 6-4 balance but when i add the 6-5 numbers the
total line will show that line since it is the most recent. Any thoughts?

"vezerid" wrote:
I assume you have a separate sheet for each fund.

In your master sheet compile, in column A:A, the names of all the
worksheets containing funds. Say you start from A2 to allow for
headers. B2 will contain the shares and C2 the balance. In B2, C2:

=LOOKUP(10^307,INDIRECT("'"&A2&"'!B:B"))
=LOOKUP(10^307,INDIRECT("'"&A2&"'!E:E"))

For the last one I am not sure which column you consider the be the
balance, but change the column in the formula.

HTH
Kostis Vezerides

On Jun 7, 6:58 am, Jon Dow wrote:
I am keeping a running log of mutual fund activity. Something like this:

Date Shares Price Daily Total Gain/Loss
6/1/2007 503.30 40.53 20,398.83 0.00
6/2/2007 503.30 40.59 0.06 20,429.03 30.20

I have several funds like this. I want to take the most recent balances and
summarize them on another sheet. So in other words, when i enter in a new
days worth of infomation, the formula on the other sheet will automatically
pick up the latest information. Something like this:

Fund A Shares Balance
Fund B Shares Balance

Any way to do this easily? Also, would there be an easy way to maybe drop in
a date and have it pick up the information for that date? I am trying to
create Quicken or Money in Excel because those 2 programs can be SOOOOO hard
to use for this stuff. Thanks.

Got it-this rocks-thanks.

The only other thing that I would like to do is to set up Sheet that pulls
information like below. However, is there another formula that if i put in
June 5 it would pull all that days info. Then if I want to see June 1, it
would do that. This may have to be another sheet but i think there should be
a way to do this. Thanks.

"vezerid" wrote:

The formula:
=LOOKUP(10^307,A:A)

will find the last (location wise) cell with any number in A:A. This
is an intentional misuse of LOOKUP that has this desirable effect.

INDIRECT builds cell references dynamically. Sort of like, instead of
necessarily looking up A:A, it can look at any range that is specified
by a string, built dynamically.

If your sheet is called "John Dow Fund" and cell A2 contains exactly
the sheet's name then the two are "equivalent"

=LOOKUP(10^307,INDIRECT("'"&A2&"'!B:B"))
=LOOKUP(10^307,'John Dow Fund'!B:B)

If a new date is added below the data in John Dow Fund'!B:B then the
formula will show the last value.

Do you have in the aux column the exact names of the sheets?

HTH
Kostis Vezerides

On Jun 7, 11:57 pm, Jon Dow wrote:
I did not understand this formula nor could I get it to work. Please explain
further. To recap, i have this in A3:d7

Date Shares Price Value
6/1/2007 400.11 34.56 13827.8016
6/2/2007 400.11 34.59 13839.8049
6/3/2007 400.11 34.06 13627.7466
6/4/2007 400.11 35.09 14039.8599

I have 10 sheets like this. I want to have a total sheet with all 10
accounts listed. I want the total line for each to show the most recent
balances. So it will show 6-4 balance but when i add the 6-5 numbers the
total line will show that line since it is the most recent. Any thoughts?

"vezerid" wrote:
I assume you have a separate sheet for each fund.

In your master sheet compile, in column A:A, the names of all the
worksheets containing funds. Say you start from A2 to allow for
headers. B2 will contain the shares and C2 the balance. In B2, C2:

=LOOKUP(10^307,INDIRECT("'"&A2&"'!B:B"))
=LOOKUP(10^307,INDIRECT("'"&A2&"'!E:E"))

For the last one I am not sure which column you consider the be the
balance, but change the column in the formula.

HTH
Kostis Vezerides

On Jun 7, 6:58 am, Jon Dow wrote:
I am keeping a running log of mutual fund activity. Something like this:

Date Shares Price Daily Total Gain/Loss
6/1/2007 503.30 40.53 20,398.83 0.00
6/2/2007 503.30 40.59 0.06 20,429.03 30.20

I have several funds like this. I want to take the most recent balances and
summarize them on another sheet. So in other words, when i enter in a new
days worth of infomation, the formula on the other sheet will automatically
pick up the latest information. Something like this:

Fund A Shares Balance
Fund B Shares Balance

Any way to do this easily? Also, would there be an easy way to maybe drop in
a date and have it pick up the information for that date? I am trying to
create Quicken or Money in Excel because those 2 programs can be SOOOOO hard
to use for this stuff. Thanks.

=VLOOKUP($K$1,INDIRECT("'"&A2&"'!A:E"),2,0)

K1 holds the date of choice. The range you search is A:F. The number 2
characterizes the column from which you want to retrieve. Change it to
5 or 6 to get another item.

HTH
Kostis

On Jun 8, 10:16 pm, Jon Dow wrote:
Got it-this rocks-thanks.

The only other thing that I would like to do is to set up Sheet that pulls
information like below. However, is there another formula that if i put in
June 5 it would pull all that days info. Then if I want to see June 1, it
would do that. This may have to be another sheet but i think there should be
a way to do this. Thanks.

"vezerid" wrote:
The formula:
=LOOKUP(10^307,A:A)

will find the last (location wise) cell with any number in A:A. This
is an intentional misuse of LOOKUP that has this desirable effect.

INDIRECT builds cell references dynamically. Sort of like, instead of
necessarily looking up A:A, it can look at any range that is specified
by a string, built dynamically.

If your sheet is called "John Dow Fund" and cell A2 contains exactly
the sheet's name then the two are "equivalent"

=LOOKUP(10^307,INDIRECT("'"&A2&"'!B:B"))
=LOOKUP(10^307,'John Dow Fund'!B:B)

If a new date is added below the data in John Dow Fund'!B:B then the
formula will show the last value.

Do you have in the aux column the exact names of the sheets?

HTH
Kostis Vezerides

On Jun 7, 11:57 pm, Jon Dow wrote:
I did not understand this formula nor could I get it to work. Please explain
further. To recap, i have this in A3:d7

Date Shares Price Value
6/1/2007 400.11 34.56 13827.8016
6/2/2007 400.11 34.59 13839.8049
6/3/2007 400.11 34.06 13627.7466
6/4/2007 400.11 35.09 14039.8599

I have 10 sheets like this. I want to have a total sheet with all 10
accounts listed. I want the total line for each to show the most recent
balances. So it will show 6-4 balance but when i add the 6-5 numbers the
total line will show that line since it is the most recent. Any thoughts?

"vezerid" wrote:
I assume you have a separate sheet for each fund.

In your master sheet compile, in column A:A, the names of all the
worksheets containing funds. Say you start from A2 to allow for
headers. B2 will contain the shares and C2 the balance. In B2, C2:

=LOOKUP(10^307,INDIRECT("'"&A2&"'!B:B"))
=LOOKUP(10^307,INDIRECT("'"&A2&"'!E:E"))

For the last one I am not sure which column you consider the be the
balance, but change the column in the formula.

HTH
Kostis Vezerides

On Jun 7, 6:58 am, Jon Dow wrote:
I am keeping a running log of mutual fund activity. Something like this:

Date Shares Price Daily Total Gain/Loss
6/1/2007 503.30 40.53 20,398.83 0.00
6/2/2007 503.30 40.59 0.06 20,429.03 30.20

I have several funds like this. I want to take the most recent balances and
summarize them on another sheet. So in other words, when i enter in a new
days worth of infomation, the formula on the other sheet will automatically
pick up the latest information. Something like this:

Fund A Shares Balance
Fund B Shares Balance

Any way to do this easily? Also, would there be an easy way to maybe drop in
a date and have it pick up the information for that date? I am trying to
create Quicken or Money in Excel because those 2 programs can be SOOOOO hard
to use for this stuff. Thanks.