i'm really struggling with a formula that seems to work in some cells and not others.
I have a table of bank transactions. Income is from clients and outgoings are from suppliers as well as charges etc.
Each supplier and customer are assigned a 'category' that matches a column of the sheet.
My formula is trying to populate the matched columns with the income or outgoings as a breakdown of the transactions.
the pseudo code is as follows;
1, If the transaction is income, lookup the customer from the customers table
2, extract the category
3,if the category matches the column heading, copy the transaction total to the column, otherwise leave blank,
4, assuming the transaction is not income
5, lookup the supplier from the suppliers table and extract the category from the supplier
6, if this category matched the column title, copy the transaction total into the cell.
The formula is copied to all the cells in the sheet covering the categories.
It works in some cells and not in others which is what i'm struggling with.
attached is a screenshot of the formula in the cells showing it working and not. There are also screenshots of the customer/supplier tables.
I hope this is enough info for suggestions of what I should try.
I have a table of bank transactions. Income is from clients and outgoings are from suppliers as well as charges etc.
Each supplier and customer are assigned a 'category' that matches a column of the sheet.
My formula is trying to populate the matched columns with the income or outgoings as a breakdown of the transactions.
the pseudo code is as follows;
1, If the transaction is income, lookup the customer from the customers table
2, extract the category
3,if the category matches the column heading, copy the transaction total to the column, otherwise leave blank,
4, assuming the transaction is not income
5, lookup the supplier from the suppliers table and extract the category from the supplier
6, if this category matched the column title, copy the transaction total into the cell.
The formula is copied to all the cells in the sheet covering the categories.
It works in some cells and not in others which is what i'm struggling with.
attached is a screenshot of the formula in the cells showing it working and not. There are also screenshots of the customer/supplier tables.
I hope this is enough info for suggestions of what I should try.