1. 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. [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

3. The ending balance is in column G and there are no blank cells in the midst of the data

4. [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

5. [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?

6. [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.

7. [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

8. [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.

9. Originally Posted by prasad' post='784643
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. [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.

11. [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. [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

13. [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

14. 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
•