Budget sheet formula

So what i am trying to do is write a formula so that when I enter a word lets say mortgage into cell a1 from sheet 2 it will take the amount that corresponds to mortgage which will be in cell a2 on sheet 2 and add that number into a1 on sheet 1. I am wanting a list so mortgage utilities phone water. Anytime I type in one of those words it knows where to add on sheet one.

Sounds like you want vlookup, or index and match.

=VLOOKUP(A2,Sheet2!$A$2:$B$100,2,0)

Sheet2 column A should contain your list of expense types, sheet 2 column B should contain the corresponding regular $ amount for each.

The formula in B2 of sheet 1 will give you the $ amount from sheet2 that corresponds to the expense type entered in A2.

To save typing, you could also use column A of sheet2 to create a data validation drop down list.

I cant get that to work. For some reason i cant upload a picture. Maybe this will help.

Sheet2 is basically a checkbook register. So sheet 2 column e4 is category ie grocery, mortgage, credit card. Column f4 is withdraw column g4 is deposit. The amounts will rarely be the same example credit card might be 5 this month 10 next month.

So i need to be able to enter a category type in e4 sheet 2. Which has a varying dollar amount entered into f4 a have it know that credit card means take f4 sheet 2 and adjust total in g25 sheet one.

Those are the actual cell numbers

Sorry if i am being redundant. I really appreciate your help

It would be easier if you could upload a sample workbook, make it as close as possible to your actual one, but using fictional data to substitute any confidential / personal details.

To attach a file, click the Go Advanced button (bottom right), then scroll down a bit and look for the 'Manage Attachments' link (use the link, not the paperclip icon, that is broken).

Highlight the cells where the formula should go, and type in the results that you expect to see based on the other data in the workbook.

It says I am not allowed to post links so hopefully the attachment is actually attached. cant see it anywhere except when I click on manage attachments.

I might just not be doing it correctly but I don't think that is what I am looking for. I don't want to have to enter a formula each time. I am wanting to type list of 20ish words into the category cell (e4 sheet 2 the link is above) and have the corresponding amounts from cells f4 and g4 sheet2 be deducted from the matching category on sheet 1.

You don't have to enter it each time, just once, then click and hold on the bottom right corner of the cell with the formula and drag down, it will fill the next one automatically.

Right I get that but this only affects that one cell I need to be able to type extra income 3 or 5 or 10 cells down and an amount into f column or g column and have it register in sheet one. I have attached a copy of the register. maybe it will make more since. You guys are awesome thank you for all the help. the register will never be the same I need it to auto populate whenever I type a word in the category section.

That is what it will do, you haven't entered 'extra income' into the register, which is why there is a zero result.

Breaking down the formula =SUMIFS(Register!G:G,Register!E:E,Budget!K6)

This will take whatever is in Budget!K6 (Extra Income) and look for each and every occurence of that in column E of the Register sheet, that get the corresponding figures from column G of the Register sheet and add them together, it doesn't matter if it is 10 rows down, 10 rows up, or 1000 rows down, it will still be included it the description matches.

If I'm following you correctly, it looks as if you want any deposit / credit entered that has a description other than 'Income' to be classed as extra income?

In which case try

=SUMIFS(Register!G:G,Register!E:E,"<>Income*")

When you enter a formula that works to criteria, you have to explicitly tell it what to look for, or in this case what to ignore. The formula cannot assume that anything which doesn't say 'Income' is actually 'Extra Income' unless you provide it with that information.

Ok that makes since. What cell do i put the formula in and do i make a new formula for when it's say groceries and mortgage or utilities.

I wish this had a video conference feature lol

That formula would be for M6, extra income.

I have just noticed that you have 2 cells for extra income in the budget sheet, but nothing to say which records in the register should be associated with which extra income cell.

For anything like groceries, utilities, etc. Use the formula already provided in post #6. This will work as long as the description in the budget sheet is the same as the one in the register.

Yippy I got it working... sort of. For some reason when the amount is populated to "budget" it changes all my font size and type and field color for that cell and formatting. How do I get the formula to register F column on "register" the same as it does G.

Not sure how that is happening. The only thing that would affect any of those things when the amount is changed would be conditional formatting, but there is none in the test file.

How do I get the formula to register F column on "register" the same as it does G.

Try changing part of the formula, from Register!G:G to Register!F:F

It works if i do f or g but not both it says that doesn't make since. Or something like that anyway. I'm not worried about the font it only does it half the time and stays once I change it back.

You can only use one column in the formula, which should be adequate for most of your sheet.

=SUMIFS(Register!F:F for anything that will be a definite outgoing payment, or SUMIFS(Register!G:G for anything that will be definite income.

If you need both then you could add the 2 together, although, as all of your entries are positive values, you would actually need to subtract to get the correct result.

=SUMIFS(Register!G:G,...)-SUMIFS(Register!F:F,...)

Got it all working like a charm thank you guys for all the help. I greatly appreciate it.