Prev: How do I resort numerical info into numerical order
Next: Sorting Cube dimension names in a Pivot Table
From: wm on 19 May 2010 09:30
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?


From: T. Valko on 19 May 2010 10:49
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" <wm(a)newsgroup.microsoft.com> wrote in message
news:O5dGmd19KHA.5464(a)TK2MSFTNGP05.phx.gbl...
>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?
>


From: wm on 19 May 2010 11:48
Sorry, I really don't understand that! What is 1E100,D:D please? Where do I
insert this formula, please?

"T. Valko" <biffinpitt(a)comcast.net> wrote in message
news:OymT2J29KHA.148(a)TK2MSFTNGP06.phx.gbl...
> 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" <wm(a)newsgroup.microsoft.com> wrote in message
> news:O5dGmd19KHA.5464(a)TK2MSFTNGP05.phx.gbl...
>>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?
>>
>
>


From: T. Valko on 19 May 2010 12:36
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" <wm(a)newsgroup.microsoft.com> wrote in message
news:eWWw$q29KHA.1888(a)TK2MSFTNGP05.phx.gbl...
> Sorry, I really don't understand that! What is 1E100,D:D please? Where do
> I insert this formula, please?
>
> "T. Valko" <biffinpitt(a)comcast.net> wrote in message
> news:OymT2J29KHA.148(a)TK2MSFTNGP06.phx.gbl...
>> 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" <wm(a)newsgroup.microsoft.com> wrote in message
>> news:O5dGmd19KHA.5464(a)TK2MSFTNGP05.phx.gbl...
>>>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?
>>>
>>
>>
>
>


From: wm on 20 May 2010 12:11
Got it now! Thanks very much.


"T. Valko" <biffinpitt(a)comcast.net> wrote in message
news:OscCzF39KHA.5476(a)TK2MSFTNGP06.phx.gbl...
> 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" <wm(a)newsgroup.microsoft.com> wrote in message
> news:eWWw$q29KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>> Sorry, I really don't understand that! What is 1E100,D:D please? Where do
>> I insert this formula, please?
>>
>> "T. Valko" <biffinpitt(a)comcast.net> wrote in message
>> news:OymT2J29KHA.148(a)TK2MSFTNGP06.phx.gbl...
>>> 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" <wm(a)newsgroup.microsoft.com> wrote in message
>>> news:O5dGmd19KHA.5464(a)TK2MSFTNGP05.phx.gbl...
>>>>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?
>>>>
>>>
>>>
>>
>>
>
>


 |  Next  |  Last
Pages: 1 2
Prev: How do I resort numerical info into numerical order
Next: Sorting Cube dimension names in a Pivot Table