checkbook coversheet

Topic

New Reply

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

Reply | Quote

Replies

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

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

Reply | Quote

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

Reply | Quote

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

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

Reply | 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

Hi franciz, what “99^99” represent to?

Reply | Quote

Hi franciz, what “99^99” represent to?

It return a very large number.

HTH

Reply | Quote

It return a very large number.

HTH

Instead it will return the last number of specified range, i think.

Reply | Quote

It return a very large number.

Instead it will return the last number of specified range, i think.

99^99 will return a very large number
LOOKUP(99^99,Sheet2!G:G) will return the last number of the specified range

Reply | Quote

99^99 will return a very large number
LOOKUP(99^99,Sheet2!G:G) will return the last number of the specified range

I am still confused with the term “large number”.

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

Reply | Quote

I am still confused with the term “large number”.

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

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.

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

Got it.

Thanks a lot

Reply | Quote

Got it.

Thanks a lot

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

Reply | Quote

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.

Reply | 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

I might be wrong but it can be more simplified with “=link”. Drag the formula upto G65536 and link it to coversheet.

Reply | Quote