What you are describing sounds a lot like the way we handle our household budget. This is possible, but quite difficult to do with an Excel spreadsheet. It is much easier to do with dedicated budgeting software designed for this purpose.
When choosing personal budgeting software, I've found that the available packages fall in two broad categories:
Some packages take what I would call a proactive approach: You enter in your bank account balances, and assign your money into spending categories. When you deposit your paycheck, you do the same thing: you add this money to your spending categories. Then when you spend money, you assign it to a spending category, and the software keeps track of your category balances. At any time, you can see both your bank balances and your spending category balances. If you need to spend money in a category that doesn't have any more money, you'll need to move money from a different category into that one. This approach is sometimes called the envelope system, because it resembles a digital version of putting your cash into different envelopes with different purposes. A few examples of software in this category are You Need a Budget (YNAB), Mvelopes, and EveryDollar.
Other packages take more of a reactive approach: You don't bother assigning a job to the money already in your bank account. Instead, you just enter your monthly income and put together a spending plan. As you spend money, you assign the transactions to a spending category, and at the end of the month, you can see what you actually spent vs. what your plan was, and try to adjust your next budget accordingly. Software that takes this approach includes Quicken and Mint.com.
I use and recommend the proactive approach, and it sounds from your question like this is the approach that you are looking for. I've used several different budgeting software packages, and my personal recommendation is for YNAB, the software that we currently use. I don't want this post to sound too much like a commercial, but I believe it will do everything you are looking for.
One of the great things about the proactive approach, in my opinion, is how credit card accounts are handled. Since your spending category balances only include real money actually sitting in an account (not projected income for the month), when you spend money out of a category with your credit card, the software deducts the money from the spending category immediately, as it is already spent. The credit card balance goes negative. When the credit card bill comes and you pay it, this is handled in the software as an account transfer from your checking account to your credit card account. The money in the checking account is already set aside for the purpose of paying your credit card bill.
Dedicated budgeting software generally has a reconcile feature that makes verifying your bank statements very easy. You just enter the date of your bank statement and the balance, and then the software shows you a list of the transactions that fall in those dates. You can check each one against the transactions on the statement, editing the ones that aren't right and adding any that are missing from the software. After everything checks out, the software marks the transactions as verified, so you can easily see what has cleared and what hasn't.
Let me give you an example to clarify, in response to your comment. This example is specific to YNAB, but other software using the same approach would work in a similar way.
Let's say that you have a checking account and a credit card account. Your checking account, named CHECKING, has $2,000 in it currently. Your credit card currently has nothing charged on it, because you've just paid your bill and haven't used it yet this billing period. YNAB reports the balance of your credit card account (we'll call this account CREDITCARD), as $0.
Every dollar in CHECKING is assigned to a category. For example, you've got $200 in "groceries", $100 in "fast food", $300 in "rent", $50 in "phone", $500 in "emergency fund", etc. If you add up the balance of all of your categories, you'll get $2,000.
Let's say that you've written a check to the grocery store for $100. When you enter this in YNAB, you tell it the name of the store, the account that you paid with (CHECKING), and the category that the expense belongs to (groceries). The "groceries" category balance will go down from $200 to $100, and the CHECKING account balance will go down from $2,000 to $1,900.
Now, let's say that you've spent $10 on fast food with your credit card. When you enter this in YNAB, you tell it the name of the restaurant, the account that you paid with (CREDITCARD), and the category that the expense belongs to (fast food). YNAB will lower the "fast food" category balance from $100 to $90, and your CREDITCARD account balance will go from $0 to $-10.
At this point, if you add up all the category balances, you'll get $1,890. And if you add up your account balances, you'll also get $1,890, because CHECKING has $1,900 and CREDITCARD has $-10. If you get your checking account bank statement at this time, the account balance of $1,900 should match the statement and you'll see the payment to the grocery store, assuming the check has cleared. And if the credit card bill comes now, you'll see the fast food purchase and the balance of $-10.
When you write a check to pay this credit card bill, you enter this in YNAB as an account transfer of $10 from CHECKING to CREDITCARD. This transfer does not affect any of your category balances; they remain the same. But now your CHECKING account balance is down to $1,890, and CREDITCARD is back to $0.
This works just as well whether you have one checking account and one credit card, or 2 checking accounts, 2 savings accounts, and 3 credit cards. When you want to spend some money, you look at your category balance. If there is money in there, then the money is available to spend somewhere in one of your accounts. Then you pick an account you want to pay with, and, looking at the account balance, if there isn't enough money in that account to pay it, you just need to move some money from another account into that one, or pick a different account. When you pay for an expense with a credit card, the money gets deducted from the category balances immediately, and is no longer available to spend on something else.
