Summarizing data to another sheet

[Jon Dow]

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.

 

[vezerid]

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

 

[Jon Dow]

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]

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

 

[Jon Dow]

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]

=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

 

[Jon Dow]

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

 

[vezerid]

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

 

[Jon Dow]

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

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"),2,0)

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

 

[David Biddulph]

If you're putting TRP directly into the formula, you can use
=VLOOKUP($F$4,TRP!A,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.