r/excel•
Slashlight

I need to filter accounts that don't have enough money to cover their expenses.

solved

I'm trying to set up a worksheet to do a very tedious task for me. The first chunk I've got figured out, but the second is proving tricky.

I'm trying to populate a list of accounts that have effectively over-drafted. I have one worksheet that updates weekly that includes all accounts and their current balances. Another worksheet includes up to two numbers per account: priority charges and general charges. Not every account will have been charged that week.

I'd like to setup a third sheet so that it'll tell me which accounts are over-drafted, but it would ideally check against both priority charges and total charges. If an account has enough money to cover Priority charges, it'll then check against General charges. If it's short, it'll be added to the list.

I'm still an excel neophyte, so I'm running into some problems. I've tried Filter functions based on SUMIF formulae, but I've had mixed results.

The pertinent columns are: Sheet 1 Accounts (A:A), Balance (F:F), Sheet 2 Accounts (B:B), Charges (D:D). Each account will be listed up to twice in Sheet 2. The first charge is Priority, the second is General.

Any help would be greatly appreciated. Even if it means I have to learn VBA.

Posted Feb 8, 2022, 4:04 AM UTC

14
Opposite-Okra-46662•

You have a couple of options. You can do a power query and link the two data sets by the account number and then add a calculation for find the updated balance. Then filter out the overages. I like to add a refresh macro to the top of the page up Update the power query.

The other option is to create a third data set using sum ifs so it auto updates based on the information in the other two tabs. Add a column to do the calculation. Then pivot off of this third dataset, filtering to only show balances less than zero. If you go this route, make sure If you add any new accounts, they are added to your third data set or link the account numbers to the list of all accounts.

3
SlashlightOP•

Solution Verified

I was hoping to make something cleaner, but I'll have to improve my skills. I ended up having the primary sheet look for the account numbers on another sheet and have formulas on the first refer to those account numbers. It isn't pretty, but it's functional. I appreciate your help.

3
Clippy_Office_AsstMOD•

You have awarded 1 point to Opposite-Okra-4666


^(I am a bot - please contact the mods with any questions. | ) ^(Keep me alive)

1
AutoModeratorMOD•

u/Slashlight - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1