| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Master Worksheets and Individual Worksheets | Excel Worksheet Functions | |||
| How use info in Excel shared worksheets to create new worksheets | Excel Worksheet Functions | |||
| how do i copy a cell in worksheets 10 to the other 9 worksheets | New Users to Excel | |||
| How do i assign the ActiveWorkbook.Worksheets to a worksheets object? | Excel Worksheet Functions | |||
| Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) | |||
Linear Mode
