+ Reply to Thread
Results 1 to 6 of 6

I am new and I need help with my budget workbook

  1. 07-30-2019, 03:06 PM #1
    Aburns1377
    Aburns1377 is offline
    Registered User
    Join Date
    10-13-2017
    Location
    Tampa, FL
    MS-Off Ver
    2013
    Posts
    6

    I am new and I need help with my budget workbook

    Hello! I am new here so howdy! I have a problem with my weekly budget sheet. I use the envelope method to track all my expenses weekly. I have one workbook with 4 sheets in it and I am trying to streamline my process so it is not so laborious a task to balance all my "envelopes" every week when I get paid. The first sheet is for Necessities and contains columns labeled with things like cargas, car repair, and mortgage stuff like that. The second sheet is the same but with long term savings columns so items like roof, new a/c, remodeling things of that nature. When ever I debit from an "envelope" I enter the total in the next row and then autosum. This part is fine and it works for me. The third sheet is for balancing of my budget. So I have all of my envelopes which are labeled in descending rows with a total of how much I have in each envelope and some of my other accounts. I then have another column with my bank accounts showing my available balances which I manually enter next to that is another column with my credit card which I enter that total every week as well. This part is fine and I have no issues keeping track of my totals from my accounts in this way and I feel it helps me to spot fraud quickly if that happens. Every week I flip through the sheets and continually go back and forth entering the new totals on this sheet. I recently discovered color coding my cells which helped me immensely to quickly see which envelope I was working with. My question is is there a formula to reference the last cell in a column from a separate sheet that will take into account that the row changes? This would really help me and save me a lot of time when doing my personal budget. Sorry for the long description of my sheet I would just share my sheet but it has personal data on it and I think it would be dumb to post that online.
    Reply With QuoteRegister To Reply
  2. 07-30-2019, 03:45 PM #2
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: I am new and I need help with my budget workbook

    Welcome to the forum.

    If I understand you correctly, you want a formula which will give you the last entry in a column. You can use this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    It works like this:
    The 'Ref sheet'!A1:A100<>"" part will return a series of True / False depending on whether there's a value in the cell.
    Dividing the array of True / False by 1 will give a further array of 1s and #Div/0 errors.
    Lookup will try to find the first number (2) in the specified array.
    It won't find it (since the highest number is a 1) so it will go to the closest matching number (1) starting from the last.
    It's that final part which makes it find the last entry in the array.
    Finally, the formula returns the place in the array (after the last comma) which is the same as that not-quite-matching-number above.

    Obviously, change the references to match your data - the sheet name, column number and end row. The end row can be as high as you like - if you expect to have hundreds of entries, use A1000, if you expect to have thousands, use A10000, etc.


    Hope that helps.


    Edit:
    ps your thread title does not really convey what your request is about, so please take a moment to amend it. It should properly explain your request, whilst not being generic (this includes function names used without an indication of what you are trying to achieve) (think what terms you might enter in an internet search to try to find an answer - your current title wouldn't feature in the results). Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
    Last edited by Aardigspook; 07-30-2019 at 04:06 PM.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
  3. 07-30-2019, 04:40 PM #3
    Aburns1377
    Aburns1377 is offline
    Registered User
    Join Date
    10-13-2017
    Location
    Tampa, FL
    MS-Off Ver
    2013
    Posts
    6

    Re: I am new and I need help with my budget workbook

    Thank you for the help I will try that!
    Reply With QuoteRegister To Reply
  4. 07-30-2019, 04:46 PM #4
    Aburns1377
    Aburns1377 is offline
    Registered User
    Join Date
    10-13-2017
    Location
    Tampa, FL
    MS-Off Ver
    2013
    Posts
    6

    Re: I am new and I need help with my budget workbook

    That worked perfectly! Thanks again!
    Reply With QuoteRegister To Reply
  5. 07-31-2019, 06:36 AM #5
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: I am new and I need help with my budget workbook

    You're welcome, glad I could help, and thanks for the rep and marking the thread as Solved.
    Reply With QuoteRegister To Reply
  6. 07-31-2019, 12:44 PM #6
    Aburns1377
    Aburns1377 is offline
    Registered User
    Join Date
    10-13-2017
    Location
    Tampa, FL
    MS-Off Ver
    2013
    Posts
    6

    Wink Re: I am new and I need help with my budget workbook

    No problem you took the time to solve my issue it was the least I could do.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trying to compare actual results to budget with multiple IS items per Budget Line
    By ppilot in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-15-2017, 03:14 AM
  2. 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
  3. [SOLVED] Budget Workbook headaches
    By cjharwood in forum Excel General
    Replies: 3
    Last Post: 07-23-2015, 05:02 PM
  4. Budget workbook, multiplying hours to make $
    By janet_omc in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-15-2013, 05:47 PM
  5. Budget Template Workbook- using for different departments...
    By brotherwo in forum Excel General
    Replies: 1
    Last Post: 11-02-2010, 04:04 PM
  6. Pulling weekly budget data into monthly budget
    By MarkRabbit in forum Excel General
    Replies: 4
    Last Post: 10-19-2008, 04:28 PM
  7. income and budget workbook
    By dtevol in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-04-2008, 01:15 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1