If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Ref in two worksheets
This formula will return the *last* numeric value from the referenced range:
=LOOKUP(1E100,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? |
#3
|
|||
|
|||
Ref in two worksheets
Sorry, I really don't understand that! What is 1E100,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) 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? |
#4
|
|||
|
|||
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) 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 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) 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? |
#5
|
|||
|
|||
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) 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 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) 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? |
#6
|
|||
|
|||
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) 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 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) 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? |
Thread Tools | |
Display Modes | |
|
|