Summarizing data to another sheet
 

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.

Summarizing data to another sheet
 

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.

Summarizing data to another sheet
 

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.

Summarizing data to another sheet
 

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.

Summarizing data to another sheet
 

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.

Summarizing data to another sheet
 

=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.

Summarizing data to another sheet
 

I get a ref error. I am not sure what &A2& is for.

"vezerid" wrote:

=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.

Summarizing data to another sheet
 

A2 contains the sheet name. I am supposing (according to your own
posts) that each sheet is named exactly as the fund name.


On Jun 11, 5:10 am, Jon Dow wrote:
I get a ref error. I am not sure what &A2& is for.

"vezerid" wrote:
=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.

Summarizing data to another sheet
 

I am sorry but this still is not working. Here is what I have in a sheet
called TRP (A3:D8):

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
6/5/2007 400.11 35.11 14047.8621

Here is the formula I have and it is giving me a #name? error ($F$4 is the
date I am looking for).

=VLOOKUP($F$4,INDIRECT("'"&TRP&"'!A:D"),2,0)

I believe this is exactly as you said but it is not working.




"vezerid" wrote:

A2 contains the sheet name. I am supposing (according to your own
posts) that each sheet is named exactly as the fund name.


On Jun 11, 5:10 am, Jon Dow wrote:
I get a ref error. I am not sure what &A2& is for.

"vezerid" wrote:
=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.

Summarizing data to another sheet
 

If you're putting TRP directly into the formula, you can use
=VLOOKUP($F$4,TRP!A:D,2,0)

The INDIRECT formula was to use cell A2 to hold the sheet name TRP, as
Kostis told you in his reply. Read carefully the replies you've had.
--
David Biddulph

"Jon Dow" wrote in message
...
I am sorry but this still is not working. Here is what I have in a sheet
called TRP (A3:D8):

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
6/5/2007 400.11 35.11 14047.8621

Here is the formula I have and it is giving me a #name? error ($F$4 is the
date I am looking for).

=VLOOKUP($F$4,INDIRECT("'"&TRP&"'!A:D"),2,0)

I believe this is exactly as you said but it is not working.

"vezerid" wrote:

A2 contains the sheet name. I am supposing (according to your own
posts) that each sheet is named exactly as the fund name.

On Jun 11, 5:10 am, Jon Dow wrote:
I get a ref error. I am not sure what &A2& is for.

"vezerid" wrote:
=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.