Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summarizing data | Excel Discussion (Misc queries) | |||
Summarizing data | Excel Discussion (Misc queries) | |||
Summarizing data on one main sheet | Excel Worksheet Functions | |||
Summarizing data | Excel Discussion (Misc queries) |