Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Getting the last value of an incrementing sheet
Greetings.
I have some separate worksheets setup for my personal finances (one for each bank account). I have the typical date, description, type of operation (debit, credit) and the amount of the operation. On the last column of the row I then update the amount of money of the previous row (it's in this column that I have the formula that will know what type of operation it is and subtracts or adds to the amount of money that is the previous row). Pretty basic stuff I guess... It's kind of a diary, where I will keep adding rows to the bottom (more recent dates at the bottom). What I'd like was to have a static page with the most current values of each worksheet - just to have an easy way to keep all my account balances at a glance. I'd have to find a way, from this static page, to go fetch the last value at the bottom of every worksheet. But alas, this would have to be dynamic: everyday I keep adding rows to every worksheet... Can anyone point me in any direction in order to do this? If any programming needs to be done, there's no problem (I'm a C++, Java and PHP programmer that doesn't know anything about MS Office), but I'd really like it to be generic (i.e. work in *every* MS Office 2003, and not just my computer because it needs a control that I have to install just to make it work)... Thanks. |
#2
|
|||
|
|||
Hi!
So, it sounds like you want to display the current balance of each account on one sheet? Assume the balance is in the same column of each sheet. I'll use column H in this example. I'll bet your sheet names are account numbers or institution names? Anyhow, list the sheet names in a range, say A1:An. In B1 enter this formula and copy down as needed: =LOOKUP(9.99999999999999E+307,INDIRECT("'"&A1&"'!H :H")) Biff "Pedro Fonseca" wrote in message om... Greetings. I have some separate worksheets setup for my personal finances (one for each bank account). I have the typical date, description, type of operation (debit, credit) and the amount of the operation. On the last column of the row I then update the amount of money of the previous row (it's in this column that I have the formula that will know what type of operation it is and subtracts or adds to the amount of money that is the previous row). Pretty basic stuff I guess... It's kind of a diary, where I will keep adding rows to the bottom (more recent dates at the bottom). What I'd like was to have a static page with the most current values of each worksheet - just to have an easy way to keep all my account balances at a glance. I'd have to find a way, from this static page, to go fetch the last value at the bottom of every worksheet. But alas, this would have to be dynamic: everyday I keep adding rows to every worksheet... Can anyone point me in any direction in order to do this? If any programming needs to be done, there's no problem (I'm a C++, Java and PHP programmer that doesn't know anything about MS Office), but I'd really like it to be generic (i.e. work in *every* MS Office 2003, and not just my computer because it needs a control that I have to install just to make it work)... Thanks. |
#3
|
|||
|
|||
Hi there!
So, it sounds like you want to display the current balance of each account on one sheet? Yup... Right on. ;) I'll bet your sheet names are account numbers or institution names? Anyhow, list the sheet names in a range, say A1:An. Right again... :) =LOOKUP(9.99999999999999E+307,INDIRECT("'"&A1&"'!H :H")) Thanks, it worked! Just one question though: what is the 9.99999999999999E+307 for? Best regards! |
#4
|
|||
|
|||
Hi!
=LOOKUP(9.99999999999999E+307,INDIRECT("'"&A1&"'!H :H")) Thanks, it worked! Just one question though: what is the 9.99999999999999E+307 for? That is the largest numerical value that can be entered into a cell. Since there is probably a 100% chance that that number will not be found in the Lookup range, the formula returns the last numeric value in the range. I'm not sure of the exact technical explanation of why it works that way. Normally, I would use a "more realistic" lookup value. Say for example, there is absolutely no way possible that any of your account balances would ever be as high as 1,000,000. Then you could use that as the lookup value: =LOOKUP(1000000,INDIRECT("'"&A1&"'!H:H")) You could adjust that down to reflect the "reality" of your specific situation. Usually when I post this type of formula (which really isn't that often) I get "scolded" for not using that unrealistic number! <g "Better to use that unrealistic number, just in case." I better get flood insurance too, even though I live in the middle of the Sahara desert ........ just in case! <vbg Biff "Pedro Fonseca" wrote in message om... Hi there! So, it sounds like you want to display the current balance of each account on one sheet? Yup... Right on. ;) I'll bet your sheet names are account numbers or institution names? Anyhow, list the sheet names in a range, say A1:An. Right again... :) =LOOKUP(9.99999999999999E+307,INDIRECT("'"&A1&"'!H :H")) Thanks, it worked! Just one question though: what is the 9.99999999999999E+307 for? Best regards! |
#5
|
|||
|
|||
=LOOKUP(9.99999999999999E+307,INDIRECT("'"&A1&"'!H :H"))
Actually, some worksheets seem to freeze for a few seconds, I'm guessing due to this long number. I tried to replace 9.99999999999999E+307 for 10000 (I was guessing that this long number would be the number of rows in any one sheet and I never have more than 1000). Worksheets never again froze for a few seconds, but I had however one worksheet that returned an odd amount for an account's balance (it wasn't the most current). I added another 0 to the 10000 and it worked again... :| So... 9.99999999999999E+307 is kind of a long number that's causing delays, but I guess 10000 is not long enough to achieve what I want in every possible case... What's probably a good bet for a number that's the minimum required to return the most current balance at the bottom of each sheet, considering that every worksheet never goes beyond 1000 rows? Thanks in advance! |
#6
|
|||
|
|||
That is the largest numerical value that can be entered into a cell. Since
there is probably a 100% chance that that number will not be found in the Lookup range, the formula returns the last numeric value in the range. I'm not sure of the exact technical explanation of why it works that way. Strange behavior, but glad it does that... :) Then you could use that as the lookup value: =LOOKUP(1000000,INDIRECT("'"&A1&"'!H:H")) Just one thing I forgot to mention: it didn't work right out of the shelf like this... I had to tweak it a bit and the working version was: =LOOKUP(1000000;INDIRECT("'A1'!H:H")) Don't know why though... Usually when I post this type of formula (which really isn't that often) I get "scolded" for not using that unrealistic number! "Better to use that unrealistic number, just in case." I better get flood insurance too, even though I live in the middle of the Sahara desert ........ just in case! Well, you never know... Probability-wise, something will be bound to happen someday... ;) "Biff" wrote in message ... Hi! =LOOKUP(9.99999999999999E+307,INDIRECT("'"&A1&"'!H :H")) Thanks, it worked! Just one question though: what is the 9.99999999999999E+307 for? That is the largest numerical value that can be entered into a cell. Since there is probably a 100% chance that that number will not be found in the Lookup range, the formula returns the last numeric value in the range. I'm not sure of the exact technical explanation of why it works that way. Normally, I would use a "more realistic" lookup value. Say for example, there is absolutely no way possible that any of your account balances would ever be as high as 1,000,000. Then you could use that as the lookup value: =LOOKUP(1000000,INDIRECT("'"&A1&"'!H:H")) You could adjust that down to reflect the "reality" of your specific situation. Usually when I post this type of formula (which really isn't that often) I get "scolded" for not using that unrealistic number! <g "Better to use that unrealistic number, just in case." I better get flood insurance too, even though I live in the middle of the Sahara desert ........ just in case! <vbg Biff "Pedro Fonseca" wrote in message om... Hi there! So, it sounds like you want to display the current balance of each account on one sheet? Yup... Right on. ;) I'll bet your sheet names are account numbers or institution names? Anyhow, list the sheet names in a range, say A1:An. Right again... :) =LOOKUP(9.99999999999999E+307,INDIRECT("'"&A1&"'!H :H")) Thanks, it worked! Just one question though: what is the 9.99999999999999E+307 for? Best regards! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Impoting data from Sheet 1 to Sheet 2 | Excel Discussion (Misc queries) | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |