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!