+ Reply to Thread
Results 1 to 14 of 14

Excel Budget Formula Help

  1. #1
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Excel Budget Formula Help

    Hey guys, I need help with this formula. Please see the screen shots. Basically I want the sheet to do the following,

    When I enter a new transaction in the "Transaction Amounts" sheet in cell A9 and select the account in c9 and category in d9, I would like g9 to show the balance for the respective c9 account. Also, to deduct from the appropriate cells B3-H3. So for this screen shot, subtract 109 from cell e9 to account CCU credit which would be C3.

    Then at the same time, I would like it to paste to the corresponding cell in the "Actual" sheet. So that would be 109 copied to Actual - Phone CCU Credit, but want it to paste in the date range closest to the transaction date.

    If someone could help me that would be great.

    I've attached the project to work on if you can help.

    Thanks.
    Kevin
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Excel Budget Formula Help

    bump, bump, bump Please help

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Excel Budget Formula Help

    Hi and welcome to the forum

    Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc.

    Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

    Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Excel Budget Formula Help

    Thanks for replying. I don't have any code, coded into this spreadsheet. What you see from the picture and file I attached is what I have. I did attach the sample file for you guys. It's called budget-planner-new.xlsx‎ For you guys to download and enter in the information I need, then I'll re-download it after you guys upload it.

    Thanks for the help.

  5. #5
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Excel Budget Formula Help

    I think this covers the first part.
    Place in G8 and copy down.

    =IFERROR(IF(E8>0,INDEX($B$3:$H$3,MATCH(C8,$B$2:$H$2,0))-E8,INDEX($B$3:$H$3,MATCH(C8,$B$2:$H$2,0))+F8),"")

    Windy

  6. #6
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Excel Budget Formula Help

    Thanks windy for helping me with this. The first part looks good =) How do you get the balances in the running total column G also to reflect the running total from the respective accounts columns C3 - H3 overall balances. I've attached a picture of the columns.

    Thanks.
    Kevin.
    Attached Images Attached Images

  7. #7
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Excel Budget Formula Help

    Not sure what you mean.
    When you say column G do you mean just G3?

    What should be here?
    Running total of what?

    Windy

  8. #8
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Excel Budget Formula Help

    Whenever I enter a new transaction amount into the sheet and it gives me a running balance for that account in column G, starting at g8, it will also need to update the corresponding account balance column B - H, Row 3.

    Then,

    It should take the transaction Date, starting at column B, row 8

    the specific Category, starting at column D, row 8

    and Debit (E8) or Credit (F8) amount, starting from column E, row 8 and 9

    and place in sheet "Actual" under corresponding "Category" from category selected in "Transactions" sheet starting at column D, row 8.


    Thanks for the help Wendy

  9. #9
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Excel Budget Formula Help

    bump bump bump bump bump bump bump, haha

  10. #10
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Excel Budget Formula Help

    bump bump bump bump bump bump bump bump bump bump bump bump bump bump

    Please help.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Excel Budget Formula Help

    Quote Originally Posted by kcstier View Post
    Whenever I enter a new transaction amount into the sheet and it gives me a running balance for that account in column G, starting at g8, it will also need to update the corresponding account balance column B - H, Row 3.
    The problem with what you are proposing is that it sets up a 'Circular Reference'. To overcome that I would suggest you put your 'Running Balance' in Row 4, populated with the array entered formula*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    I am attaching only the first sheet of your workbook with the formula applied. I will work on the rest of your request later if no one else has posted a solution by then.
    Let me know if you have any questions.
    EDIT: to make this work Windy's formula needs to be modified as follows:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JeteMc; 07-23-2016 at 10:19 AM. Reason: Added formula
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Excel Budget Formula Help

    Quote Originally Posted by kcstier View Post
    It should take the transaction Date, starting at column B, row 8
    the specific Category, starting at column D, row 8
    and Debit (E8) or Credit (F8) amount, starting from column E, row 8 and 9
    and place in sheet "Actual" under corresponding "Category" from category selected in "Transactions" sheet starting at column D, row 8.
    Try pasting this formula in Q31 then drag down and across to AB41:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    You'll need to copy and paste the formula into the top left cell of each section of the 'Actual' sheet and then drag down and across to populate that section. I have populated the 'Income' and 'Home Expenses' sections.
    Note: I modified the custom format to accommodate negative numbers.
    Let me know if you have any questions.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Excel Budget Formula Help

    Thank you for the help JeteMc. It appears to be working well. I'm now going to figure out a function that pastes the data from the transaction sheet into the respective cells in the "actual" sheet. Thank you sir.

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Excel Budget Formula Help

    You're Welcome and thank you for the feedback. If that answers your original question, please select Thread Tools from the menu link above your first post in the thread and mark this thread as SOLVED. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Nested IF statement to show under budget, within a % of budget, over budget
    By clafleur in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2015, 10:36 AM
  2. budget formula. 2 different formulas for yearly budget SUMIF?
    By italianstallion in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-04-2013, 05:20 AM
  3. [SOLVED] IF function to define over budget or under budget
    By mrose in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 06-15-2012, 02:57 PM
  4. Pulling weekly budget data into monthly budget
    By MarkRabbit in forum Excel General
    Replies: 4
    Last Post: 10-19-2008, 04:28 PM
  5. Excel Budget Formula
    By ExcelNewby in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-01-2007, 09:30 AM
  6. Divide Monthly Sales Budget to Day budget
    By Benedikt Fridbjornsson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-11-2006, 03:25 PM
  7. [SOLVED] Divide Monthly Sales Budget to Day Budget
    By Benedikt Fridbjornsson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2006, 11:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1