Excel Help

I am trying to make a budget in excel. I am running into a few problem.

1. I have expense categories, and there are limits in each category. I have made a column of cells with the category names and their limits next to them. In a separate area, I have created drop-down lists to select the category for each expense entered. The idea is that when you enter the expense and then select the category from the drop down, the balance (how much over/under the limit you are in that category) is calculated and displayed next to the category name, keeping you updated on how much you have been spending in each category. I imagine this involves some sort of if/then, but I don't know how to make an if/then that can do an IF on a whole column. Maybe I can do it in the running balance area? I have no clue.
2. I have a single cell with a large-formatted number to keep track of the overall balance that needs to be updated each time you add a row of expense information. Can't do that either.
3. Finally, I want a block of cells on the right side of the worksheet (the block with the categories and updated category balance) to stay in place and now move when I scroll the sheet. I know I can freeze cells into the upper-left corner of the sheet, but I have googled and googled and can't find how to freeze cells in other places on the worksheet.

And that's where I'm stuck. Any insights?Thanks!Edited by Jon, : No reason given.

I'm a little rusty on this, but I think you want to look at "subtotal" "sumif" and "lookup" functionssubtotal does not get included in total sum even if within the sum field. sumif has an if test argument - ie ... SUMIF(range,criteria,sum_range)the "range" is the category (house, car, work), "criteria" is the test (words in quotes .. ie "house") and "sum-range" is the expense data being summed.for instance I get where the formula in the top subtotal cell is
=SUMIF(D$30$33,D27,C$30:C$33)
D$30$33 is the range with the check amounts
D27 is the test criteria (legal in the top box)
C$30:C$33 is the range with the test labeliirc the criteria can also be formula like [>$100.00] but I haven't tested thatLet me know if that worksEdited by RAZD, : iircEdited by RAZD, : exampleEdited by RAZD, : No reason given.

You can only freeze top lines and\or left side column so you will need to work that into your format.The other option is to split the screen, and you can do a 4-block split -- see if that worksEnjoy

Set you sum ranges to extend below your current rows and in the last fill it with x's or shade them so you know which it is ..Then when you want to add rows highlight as many rows as you want starting with the bottom row and then use INSERT ROWThat should include the inserted rows in the ranges and leave your xxx\shaded row at the bottom -- because you are stretching the ranges rather than adding to themEnjoyEdited by RAZD, : ...

I sort of rigged something up before you replied. I'm not sure if what you have would have worked or not.What I ended up going with was a dummy sheet (that I then hid) with a separate column of If/then for each category possible. I then dragged the formula down to cover about 1500 rows, which should be good for a monthly entry. An example in one cell would be:Where 'January' is the main sheet I enter my data on, the G column contains the drop-down list with each category and the F column contains the expense itself. The values are relative, so the cell below it has $G13 and $F13. So Fuel takes the whole A column. In the B column is the conditional to test the G row from the main sheet for 'Groceries (F)', and so on for nine columns each testing whether the category selected matches and adding to their column if it does.Because the G column on the main sheet has a data validation set up, there is no way to enter a value that doesn't occur somewhere in one of the If/thens, so there is a null value in the 'then' spot on each conditional, meaning that for every category that wasn't selected (all but one) a value of FALSE is displayed.Back on the main sheet, the cell that displays the balance remaining in each category simply sums up the whole row for that category from the dummy sheet and subtracts it from a predefined limit on a separate sheet containing the limits (which makes it easy to change the limit once for the whole year, rather than changing them for each month (for example, if the Internet bill goes up)). So those look like:Where B2 is the limit and 'JanCalc' is the dummy sheet the sum of whose A column is subtracted from the limit (meaning the whole value is recalculated each time you add to the report).It was a real pain in the ass to set up, but it works pretty smooth in practice. Because you can hide the dummy sheets, all the real work goes on behind the scenes and all I see is the big red negative telling me I've spent too much at the coffee shop this month and have no money left for a haircut...

Yeah... that's what I feared I just moved my stuff around. Because I needed my categories box to not scroll left-right or up-down, the split feature wouldn't work; only the freeze pain function lets you keep a set of cells from moving, but you can only freeze the upper-left pain.

Because the expense and income are stored in the same column, I found I could use a simple =SUM(F:F) formula to keep the running balance updated. Since it is a big number at the top of the sheet, I just got rid of the balance shown at the end of each entry line, since only the most current balance matters anyway.Thanks for all the advice!I'm sure I will find a use for that SUMIF command. I might even play with it a little to see if I can use it to make a more simplified budgeting file in the future...

Holy shit!The SUMIF command works beautifully and the whole thing is contained in the cell I want to display the balance in, so there's no dummy sheet!I'm almost tempted to redo the whole thing just to make it more simple and elegant (and open faster; twelve sheets with nearly 15,000 cell values to calculate kind of slows things down).I'm gonna do a little more testing before I commit to changing the whole thing over, but this really seems like you found the fix.Yep. Works like a charm. Now I will be redoing several workbooks. But it will be so worth it to save the space and for the whole thing to look cleaner.The value I used was:Where B3 is the limit, G is the column with the category listed, and F is the column with the expense value. So lovely!You really were a big help on this!Fixed the whole thing in eight different workbooks in just twenty minutes. Worth every second I spent too.Edited by Jon, : No reason given.Edited by Jon, : No reason given.Edited by Jon, : No reason given.

Another option is to show two sheets in your view while working on entries, but the problem with that is that the printout will be different from what you see ... and you need a wide screen.Glad I could help.You can also do conditional formating to make cells change text color, background color, font, etc based on test criteria (ie red text when you exceed allowable).EnjoyEdited by RAZD, : ..