Retrieve value from a cell

[Alex Sanchez]

Hello,

What I have is a Check Register and I want to have the current balance on the side.
I want the Current Balance on H15. The current balance is on G12. So, on H15 I tried =G12, but this will change as I add more transaction. Is there a way I can have my current balance updated as I add transactions?

Thanks,
Alex

 

[Jonmo1]

Is the current balance simply the last entry (bottom) of column G?

Try

=LOOKUP(9.99999999999999E+307, G:G)

 

[Scott Huish]

If you are always trying to pull the last value from column G, use this:

=LOOKUP(9.99999999999E+307,G:G)

 

[Alex Sanchez]

Thank you jonmo1. It worked.
If it is not too much to ask, could you please explain the formula a little bit? What are all those 9s for and the +307?

Thanks again,
Alex

 

[Jonmo1]

9.99999999999999E+307 is just scientific notation of a very large number.
I'm no expert on scientific notation, but it's something like
9.99999etc Multiplied by (10+307 zeros)
It's listed in the help files as the largest number a cell can hold.
So it's garunteed to be equal to or larger than ANY number in your range.

Lookup does a binary type search, so since the lookup # is larger than any number in the range, it then returns the last number.

Hope that helps

 

[Alex Sanchez]

Thank you very much for the detailed explanation Jonmo1.

-Alex

 

[T. Valko]

Hello,

What I have is a Check Register and I want to have the current balance on the side.
I want the Current Balance on H15. The current balance is on G12. So, on H15 I tried =G12, but this will change as I add more transaction. Is there a way I can have my current balance updated as I add transactions?

Thanks,
Alex

Try this "KISS" version:

=LOOKUP(1E100,G:G)

If you don't get the same result as the pedantic version:

=LOOKUP(9.99999999999999E+307, G:G)

I'll buy your beer for the rest of your life!

 

[Alex Sanchez]

Thank you T. Valko.

 

[T. Valko]

Thank you T. Valko.

You're welcome!