+ Reply to Thread
Results 1 to 6 of 6

Getting the last value of an incrementing sheet

  1. #1
    Pedro Fonseca
    Guest

    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. #2
    Biff
    Guest

    Re: Getting the last value of an incrementing sheet

    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" <nospam@pedrofonseca.com> wrote in message
    news:15109a72.0505091416.6e4ee53e@posting.google.com...
    > 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. #3
    Pedro Fonseca
    Guest

    Re: Getting the last value of an incrementing sheet

    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. #4
    Biff
    Guest

    Re: Getting the last value of an incrementing sheet

    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" <nospam@pedrofonseca.com> wrote in message
    news:15109a72.0505101613.3bcd0e57@posting.google.com...
    > 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. #5
    Pedro Fonseca
    Guest

    Re: Getting the last value of an incrementing sheet

    > =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. #6
    Pedro Fonseca
    Guest

    Re: Getting the last value of an incrementing sheet

    > 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" <biffinpitt@comcast.net> wrote in message news:<uaa4vPeVFHA.2420@TK2MSFTNGP12.phx.gbl>...
    > 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" <nospam@pedrofonseca.com> wrote in message
    > news:15109a72.0505101613.3bcd0e57@posting.google.com...
    > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1