+ Reply to Thread
Results 1 to 8 of 8

How to display multiple bills from a budget until the next pay date

  1. #1
    Registered User
    Join Date
    12-16-2018
    Location
    Utah, USA
    MS-Off Ver
    Google Sheets/ Numbers
    Posts
    21

    How to display multiple bills from a budget until the next pay date

    Hello, I am trying to make a budget sheet that can display all the bills based on a selected pay period.
    I currently have 3 tabs.
    My 1st tab only displays: monthly bills, due dates, if its auto or not, and if is is auto where it comes out from
    , as well as the name of the bill such as Mortgage, or FuboTV.
    Tab 2 displays: my respective pay periods for my job, the pay date, and the amount I will receive whether estimated or actual(which I type in).
    Tab 3 is supposed to be able to select the pay period from a drop down menu, i.e. PP 2, 6, 21, up to 26, then show me the pay period, the date i get paid, the amount i get paid or got paid if i look into the past, and also display all the bills for those 2 weeks


    For example:
    On tab 3:
    If i select Pay Period 21 from my drop down, which corresponds to the pay date: Oct 20th, 2020. I want the bills for the next 14 days to be displayed based on my main budget sheet page.
    So, Oct 20th - Nov 2nd Bills should be displayed below the drop down
    So if i have bills on Oct 23rd, Oct 26th, and Nov 2nd, I need all 3 of those bills to display. HOW DO I DO THIS?

    This was just an example, attached is my actual sheet, and the 3rd tab is almost done, i just need to somehow create a filter, or match or vlookup that can somehow return and display a spilled list of my bills for those 2 weeks.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-16-2018
    Location
    Utah, USA
    MS-Off Ver
    Google Sheets/ Numbers
    Posts
    21

    Re: How to display multiple bills from a budget until the next pay date

    So far ive got this as a formula to show the correct bills for Pay Period 21.

    =FILTER('Main Budget'!E3:H19,('Main Budget'!E3:E19>=C2)*('Main Budget'!E3:E19<=D2))

    the only problem with this, is that it is not dynamic. It will only display the correct bills for the bills of october, but once i hit pay period 22, it should be showing novembers bills, but it does not because on the main budget sheet (tab 1), they are coded as october, when really they need to be coded as the 1st of the month, the 3rd of the month, etc... not just the 1st of october. any ideas on how to make all this dynamic so my formula works?

  3. #3
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to display multiple bills from a budget until the next pay date

    Hi
    Use a aggregate function to do that

    To optimize use a helper column. In H2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In I2 and copy down and forward
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See the file
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-16-2018
    Location
    Utah, USA
    MS-Off Ver
    Google Sheets/ Numbers
    Posts
    21

    Re: How to display multiple bills from a budget until the next pay date

    Well, you lost me at Use the aggregate function... lol. Ill google that, but i did what you told me to do and copy/pasted your formulas to H2, and I2 respectively and no joy. Also, what is the helper column doing? Thx so much for your reply!!

    EDIT: I believe i did what you wanted me to do, but I never used the code you said to use in H2 and copy down. I just typed out 1-17 and the other formula worked but its not doing what im asking for. Im trying to display the dates of bills for that specific pay period, and by typing out a helper column (1-17), it just shows me all of the bills.
    Last edited by skyep2058; 10-14-2020 at 05:27 AM.

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to display multiple bills from a budget until the next pay date

    Hi @Skyep2058

    The help column shows us the row number of the table that checks the condition

    ('Main Budget'!$E$3:$E$32>='PP Bills'!$C$2)*('Main Budget'!$E$3:$E$32<='PP Bills'!$D$2)

    In the file I sent you can change 'Pay period'. Column I of your 'PP Bills' sheet will show only the dates that respect that condition.

    You see the file?

  6. #6
    Registered User
    Join Date
    12-16-2018
    Location
    Utah, USA
    MS-Off Ver
    Google Sheets/ Numbers
    Posts
    21

    Re: How to display multiple bills from a budget until the next pay date

    yes i see your previous file, but i cannot get your function:

    =IFERROR(AGGREGATE(15,6,ROW($A$3:$A$32)/(('Main Budget'!$E$3:$E$32>='PP Bills'!$C$2)*('Main Budget'!$E$3:$E$32<='PP Bills'!$D$2)),ROWS($A$1:$A1))-2,"")

    to work correctly. p.s. im on a macbook and do not have the excel software installed. I only use google sheets online. when i enter you function it does nothing. is there an error in it? idk. so basically when i open your file, it opens numbers and says that function could not be converted.

  7. #7
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to display multiple bills from a budget until the next pay date

    Hi

    Thanks for the information on your profile: Excel 365!

  8. #8
    Registered User
    Join Date
    12-16-2018
    Location
    Utah, USA
    MS-Off Ver
    Google Sheets/ Numbers
    Posts
    21

    Re: How to display multiple bills from a budget until the next pay date

    ? google sheets allows your function to be entered when i copy/paste it, but it does not work. How do i get it to work?

+ 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. Replies: 2
    Last Post: 03-15-2017, 03:14 AM
  2. [SOLVED] Monthly calendar of bills - pull out bills for a month
    By Keelin in forum Excel General
    Replies: 8
    Last Post: 04-10-2016, 04:57 PM
  3. [SOLVED] Need Budget Template for Bills, expenses, credit card balances wh.
    By Allicia in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-01-2014, 01:24 PM
  4. Bi-weekly budget - formula for entering bills by due date
    By Hstclair05 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 02-01-2013, 06:39 PM
  5. [SOLVED] Budget Remaining Database with multiple Projects split by date
    By Dhanaway02 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 11-14-2012, 05:37 PM
  6. Replies: 14
    Last Post: 03-25-2011, 08:31 AM
  7. How to get date to auto change for bills
    By skullzy in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 11-29-2009, 04:55 PM

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