Hi,
I'd be grateful for any advice on a problem that I've been struggling with - despite searching high and low for clues.
I have several columns in a Data Table that provide running account balances, with transactions on each line crediting or debiting one or more accounts. I have a column for the transaction values and one specifying the transaction type (e.g. AccountX Interest).
I 'm trying to simplify the code used as my workbook is slowing down - so I'm keen to use non-volatile functions etc. and minimise the work Excel has to do. I'd also like to make the workbook easier to edit if the accounts change etc..
I have (in a separate sheet) created Defined Name lists of the transaction types that should trigger either credits or debits to/from each account (e.g. called credits_AccountX and debits_AccountX).
I have also used a Defined Name to create a CellAbove reference, which works fine when tested separately.
I am trying to build a formula for the cells in the account columns that 'says':
1. If the Transaction type is listed in the credits_AccountX Defined Name list, add the Transaction value to the balance in the cell above
2. If the Transaction type is listed in the in the debits_AccountX Defined Name list, subtract the Transaction value from the balance in the cell above
3. If neither of these applies, use the balance in the cell above
I am currently trying to make the following formula work:
=
IF(OR([@[Transaction type]]=credits_AccountX), CellAbove+[@[Transaction value]],
IF(OR([@[Transaction type]]=debits_AccountX), CellAbove-[@[Transaction value]],
CellAbove
))
Looking at the formula builder, this seems to be returning the correct data, but it shows as a #VALUE! error on the sheet.
I'm using Excel for Mac 2016.
Can anyone see where I'm going wrong, or suggest an alternative approach?
Many thanks indeed!
Andrew
I'd be grateful for any advice on a problem that I've been struggling with - despite searching high and low for clues.
I have several columns in a Data Table that provide running account balances, with transactions on each line crediting or debiting one or more accounts. I have a column for the transaction values and one specifying the transaction type (e.g. AccountX Interest).
I 'm trying to simplify the code used as my workbook is slowing down - so I'm keen to use non-volatile functions etc. and minimise the work Excel has to do. I'd also like to make the workbook easier to edit if the accounts change etc..
I have (in a separate sheet) created Defined Name lists of the transaction types that should trigger either credits or debits to/from each account (e.g. called credits_AccountX and debits_AccountX).
I have also used a Defined Name to create a CellAbove reference, which works fine when tested separately.
I am trying to build a formula for the cells in the account columns that 'says':
1. If the Transaction type is listed in the credits_AccountX Defined Name list, add the Transaction value to the balance in the cell above
2. If the Transaction type is listed in the in the debits_AccountX Defined Name list, subtract the Transaction value from the balance in the cell above
3. If neither of these applies, use the balance in the cell above
I am currently trying to make the following formula work:
=
IF(OR([@[Transaction type]]=credits_AccountX), CellAbove+[@[Transaction value]],
IF(OR([@[Transaction type]]=debits_AccountX), CellAbove-[@[Transaction value]],
CellAbove
))
Looking at the formula builder, this seems to be returning the correct data, but it shows as a #VALUE! error on the sheet.
I'm using Excel for Mac 2016.
Can anyone see where I'm going wrong, or suggest an alternative approach?
Many thanks indeed!
Andrew