Results 1 to 14 of 14
Thread: checkbook coversheet

20090714, 11:30 #1
 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

20090714, 13:01 #2
 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='14Jul2009 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 dataHope 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

20090714, 13:20 #3
 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

20090714, 13:34 #4
 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='15Jul2009 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 rangeHope 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

20090715, 00:25 #5
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='franciz' post='784543' date='14Jul2009 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

20090715, 01:21 #6
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='franciz' post='784543' date='14Jul2009 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

20090715, 14:14 #7
 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='15Jul2009 12:25']Hi franciz, what "99^99" represent to?[/quote]
It return a very large number.
HTHHope 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

20090716, 00:27 #8
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='franciz' post='784778' date='15Jul2009 18:14']It return a very large number.
HTH[/quote]
Instead it will return the last number of specified range, i think.Regards
Prasad

20090716, 02:23 #9
 Join Date
 Nov 2001
 Posts
 10,550
 Thanks
 0
 Thanked 7 Times in 7 Posts

20090716, 02:39 #10
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='StuartR' post='784863' date='16Jul2009 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

20090716, 03:31 #11
 Join Date
 Nov 2001
 Posts
 10,550
 Thanks
 0
 Thanked 7 Times in 7 Posts
[quote name='prasad' post='784866' date='16Jul2009 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.

20090716, 04:59 #12
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='StuartR' post='784868' date='16Jul2009 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 lotRegards
Prasad

20090716, 14:09 #13
 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='16Jul2009 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 handyHope 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

20090716, 14:16 #14
 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.