Getting the last value of an incrementing sheet

[Pedro Fonseca]

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.

 

[Biff]

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]

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!

 

[Biff]

Hi!

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]

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

 

[Pedro Fonseca]

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