Ref in two worksheets
 

I have a bank statement on a worksheet and want to show the current balance
in another workbook.

I can copy a link for the current balance to the other workbook but do not
know how to update it when the bank statement is updated (i.e. in the next
row down).

Example:

Bank statement
Date Debit Credit Balance
15/5 0.00 100.00 100.00
16/5 50.00 - 50.00

It is this last total (50.00) which I need to update on the second workbook.

Is this possible please?

Ref in two worksheets
 

This formula will return the *last* numeric value from the referenced range:

=LOOKUP(1E100,D:D)

If you want this to link to a different file then just include the path and
sheet name.


--
Biff
Microsoft Excel MVP


"wm" wrote in message
...
I have a bank statement on a worksheet and want to show the current balance
in another workbook.

I can copy a link for the current balance to the other workbook but do not
know how to update it when the bank statement is updated (i.e. in the next
row down).

Example:

Bank statement
Date Debit Credit Balance
15/5 0.00 100.00 100.00
16/5 50.00 - 50.00

It is this last total (50.00) which I need to update on the second
workbook.

Is this possible please?

Ref in two worksheets
 

Sorry, I really don't understand that! What is 1E100,D:D please? Where do I
insert this formula, please?

"T. Valko" wrote in message
...
This formula will return the *last* numeric value from the referenced
range:

=LOOKUP(1E100,D:D)

If you want this to link to a different file then just include the path
and sheet name.


--
Biff
Microsoft Excel MVP


"wm" wrote in message
...
I have a bank statement on a worksheet and want to show the current
balance in another workbook.

I can copy a link for the current balance to the other workbook but do
not know how to update it when the bank statement is updated (i.e. in the
next row down).

Example:

Bank statement
Date Debit Credit Balance
15/5 0.00 100.00 100.00
16/5 50.00 - 50.00

It is this last total (50.00) which I need to update on the second
workbook.

Is this possible please?

Ref in two worksheets
 

Let's assume your account balance is in column D of a sheet named Register.

The sheet named Register is just like a typical checkbook register. You
record transactions and as you do the account balance changes.

This formula will return the *last* (bottom-most) numeric value from column
D (the balance column) of the sheet named Register:

=LOOKUP(1E100,Register!D:D)

Let's assume Register column D looks like this:

D1 = column header = Balance
D2 = 10,000.00
D3 = 14,127.22

With that data, the formula will return 14127.22

Where you put the formula is up to you. You just can't enter the formula in
the same column that you're referencing in the formula.

--
Biff
Microsoft Excel MVP


"wm" wrote in message
...
Sorry, I really don't understand that! What is 1E100,D:D please? Where do
I insert this formula, please?

"T. Valko" wrote in message
...
This formula will return the *last* numeric value from the referenced
range:

=LOOKUP(1E100,D:D)

If you want this to link to a different file then just include the path
and sheet name.


--
Biff
Microsoft Excel MVP


"wm" wrote in message
...
I have a bank statement on a worksheet and want to show the current
balance in another workbook.

I can copy a link for the current balance to the other workbook but do
not know how to update it when the bank statement is updated (i.e. in
the next row down).

Example:

Bank statement
Date Debit Credit Balance
15/5 0.00 100.00 100.00
16/5 50.00 - 50.00

It is this last total (50.00) which I need to update on the second
workbook.

Is this possible please?

Ref in two worksheets
 

Got it now! Thanks very much.


"T. Valko" wrote in message
...
Let's assume your account balance is in column D of a sheet named
Register.

The sheet named Register is just like a typical checkbook register. You
record transactions and as you do the account balance changes.

This formula will return the *last* (bottom-most) numeric value from
column D (the balance column) of the sheet named Register:

=LOOKUP(1E100,Register!D:D)

Let's assume Register column D looks like this:

D1 = column header = Balance
D2 = 10,000.00
D3 = 14,127.22

With that data, the formula will return 14127.22

Where you put the formula is up to you. You just can't enter the formula
in the same column that you're referencing in the formula.

--
Biff
Microsoft Excel MVP


"wm" wrote in message
...
Sorry, I really don't understand that! What is 1E100,D:D please? Where do
I insert this formula, please?

"T. Valko" wrote in message
...
This formula will return the *last* numeric value from the referenced
range:

=LOOKUP(1E100,D:D)

If you want this to link to a different file then just include the path
and sheet name.


--
Biff
Microsoft Excel MVP


"wm" wrote in message
...
I have a bank statement on a worksheet and want to show the current
balance in another workbook.

I can copy a link for the current balance to the other workbook but do
not know how to update it when the bank statement is updated (i.e. in
the next row down).

Example:

Bank statement
Date Debit Credit Balance
15/5 0.00 100.00 100.00
16/5 50.00 - 50.00

It is this last total (50.00) which I need to update on the second
workbook.

Is this possible please?

Ref in two worksheets
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"wm" wrote in message
...
Got it now! Thanks very much.


"T. Valko" wrote in message
...
Let's assume your account balance is in column D of a sheet named
Register.

The sheet named Register is just like a typical checkbook register. You
record transactions and as you do the account balance changes.

This formula will return the *last* (bottom-most) numeric value from
column D (the balance column) of the sheet named Register:

=LOOKUP(1E100,Register!D:D)

Let's assume Register column D looks like this:

D1 = column header = Balance
D2 = 10,000.00
D3 = 14,127.22

With that data, the formula will return 14127.22

Where you put the formula is up to you. You just can't enter the formula
in the same column that you're referencing in the formula.

--
Biff
Microsoft Excel MVP


"wm" wrote in message
...
Sorry, I really don't understand that! What is 1E100,D:D please? Where
do I insert this formula, please?

"T. Valko" wrote in message
...
This formula will return the *last* numeric value from the referenced
range:

=LOOKUP(1E100,D:D)

If you want this to link to a different file then just include the path
and sheet name.


--
Biff
Microsoft Excel MVP


"wm" wrote in message
...
I have a bank statement on a worksheet and want to show the current
balance in another workbook.

I can copy a link for the current balance to the other workbook but do
not know how to update it when the bank statement is updated (i.e. in
the next row down).

Example:

Bank statement
Date Debit Credit Balance
15/5 0.00 100.00 100.00
16/5 50.00 - 50.00

It is this last total (50.00) which I need to update on the second
workbook.

Is this possible please?