Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a savings book register in excel, the names of all 5 kids as worksheet names. Each kid enters the deposits and withdraws under there tab or worksheet. The formula is Balance + deposits- withdraws. Would it be possible to have something like a cover sheet based on the worksheets that would show the ending or current balance for all the kids on one sheet? We are using excel 2000

  2. #2
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='buckshot' post='784525' date='14-Jul-2009 23:30']I have a savings book register in excel, the names of all 5 kids as worksheet names. Each kid enters the deposits and withdraws under there tab or worksheet. The formula is Balance + deposits- withdraws. Would it be possible to have something like a cover sheet based on the worksheets that would show the ending or current balance for all the kids on one sheet? We are using excel 2000[/quote]

    1) which column is your ending balance
    2) will there be blank cells in the midst of the data
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The ending balance is in column G and there are no blank cells in the midst of the data

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='buckshot' post='784540' date='15-Jul-2009 01:20']The ending balance is in column G and there are no blank cells in the midst of the data[/quote]


    perhap this

    =LOOKUP(99^99,Sheet2!G:G)

    change the sheet reference accordingly and this will give you the last enter value in col G
    This should works even with blanks in the midst of the range
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='franciz' post='784543' date='14-Jul-2009 17:34']perhap this

    =LOOKUP(99^99,Sheet2!G:G)

    change the sheet reference accordingly and this will give you the last enter value in col G
    This should works even with blanks in the midst of the range[/quote]
    Hi franciz, what "99^99" represent to?
    Regards
    Prasad

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='franciz' post='784543' date='14-Jul-2009 18:34']perhap this

    =LOOKUP(99^99,Sheet2!G:G)

    change the sheet reference accordingly and this will give you the last enter value in col G
    This should works even with blanks in the midst of the range[/quote]
    I might be wrong but it can be more simplified with "=link". Drag the formula upto G65536 and link it to coversheet.
    Regards
    Prasad

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='784643' date='15-Jul-2009 12:25']Hi franciz, what "99^99" represent to?[/quote]


    It return a very large number.

    HTH
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='franciz' post='784778' date='15-Jul-2009 18:14']It return a very large number.

    HTH[/quote]
    Instead it will return the last number of specified range, i think.
    Regards
    Prasad

  9. #9
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Quote Originally Posted by prasad' post='784643 View Post
    It return a very large number.
    [quote name='prasad' post='784860' date='16-Jul-2009 05:27']Instead it will return the last number of specified range, i think.[/quote]
    99^99 will return a very large number
    LOOKUP(99^99,Sheet2!G:G) will return the last number of the specified range

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='StuartR' post='784863' date='16-Jul-2009 06:23']99^99 will return a very large number
    LOOKUP(99^99,Sheet2!G:G) will return the last number of the specified range[/quote]
    I am still confused with the term "large number".

    It returns the value in case balance is a -(ve) figure.
    Regards
    Prasad

  11. #11
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='prasad' post='784866' date='16-Jul-2009 07:39']I am still confused with the term "large number".

    It returns the value in case balance is a -(ve) figure.[/quote]
    99^99 is the number
    99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99

    This really is a VERY LARGE number.

    The lookup function attempts to find this number in column G:G of Sheet2. When it fails to find the number it returns the last number in the column instead.

    This works because Lookup expects the numbers in G:G to be already sorted in numerical order, and when it can't find the number it is looking for it returns what it thinks is the nearest match in the list.

  12. #12
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='StuartR' post='784868' date='16-Jul-2009 07:31']99^99 is the number
    99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99 x 99

    This really is a VERY LARGE number.

    The lookup function attempts to find this number in column G:G of Sheet2. When it fails to find the number it returns the last number in the column instead.

    This works because Lookup expects the numbers in G:G to be already sorted in numerical order, and when it can't find the number it is looking for it returns what it thinks is the nearest match in the list.[/quote]
    Got it.

    Thanks a lot
    Regards
    Prasad

  13. #13
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='784875' date='16-Jul-2009 16:59']Got it.

    Thanks a lot[/quote]


    Stuart,

    thanks for the explanation.

    Prasad, in fact, there are many large number that can be use.
    I just happen to use this because it handy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'll often see 9.99999999999999E+307 in formulas of this kind, because that is the largest number that can be entered in a cell in Excel.

Posting Permissions

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