Recurring Values on Specific Dates Across Worksheets

All,

I am an Excel novice, perhaps even less than a novice honestly, and I've been trying to figure this one out for a couple weeks now.

I have created a "budget calendar" to keep track of bills, their daily amounts, and their weekly amounts. I started my week on Thursdays- which is why I couldn't simply use a template. I have the months organized into their own separate sheets.

Everything is set up and ready for bills and values to be added (an example of this can be seen in cell H10 and I10 of the attached document).

My questions is this: How do I set up a bill due on the 1st of the month to recur for each of the following months, in their separate worksheets? Being that each month has the dates in different cell values per sheet, I cannot simply enter values and drag. I know that I can link February sheet to January sheet- March to Feb, etc. (so that each month takes into account any changes in bills based on the previous month) Is there an easier way to recur these values?

To show exactly what I am wanting to do, in January on cell H10 and I10 I manually entered "car" and "580"- I did the same on February in cell N10 and O10. This is what I want it to look like, but is there a way to formulate this so these names and values will populate on the day the bills are due (sample being due on the 1st) into the rest of the months on their own separate work sheet? Linking cells to one another per worksheet is incredibly tedious, and then if a due date changes... I'll have to do it all over again. Any ideas?



Attachment 832794

A date falling on a weekend wouldn't change anything in order to keep things consistent. If for whatever reason a bill due date needs to change, I would simply alter that one date in whichever month it is in.

Basically, review the workbook on the first of each month, if any changes need to be made, I make it for that one worksheet, and it would auto-populate for the rest of the months/sheets.

Aligw, was that question directed towards me?

Aligw,

I've created the list of values, dates, and bill titles that could be used to populate a calendar or form a calendar. By this point I've tried several strategies. I believe excel could populate the calendar from my table. I've attempted x lookup, filter, and power pivot tables. I've attempted "get data from table" and direct cell references. I've also indicated what date of the month a bill applies in a separate table and attempted to connect the date to the calendar sheets with no success.
I cannot keep the values consistently on the correct date month to month. I'm not sure how to form a relationship between the dates and the titles rather than the actual cells.

So unfortunately, I cannot answer your question.

Thank you both for your help. Your suggestions and shared template pointed me into the right direction. The template you provided, JohnTopley, gave me the idea to simply use one worksheet for a dynamic calendar. This enabled me to create relationships between certain cells to accurately populate the calendar per month. Once I tried to use the match function and I couldn’t get it to work, it ended up leading me to the path of XLOOKUP. I was also able to create a relationship between the XLOOKUP function and the separate table on a separate worksheet (“Master List Program”) I created due to AliGW mentioning I would need it later on.

The table isn’t as clean as I would like it to be, but I’m working on that with conditional formatting. The reason the dates start on the 20th of one month and go through a bit of a weird range is to ensure that the beginning and end of months will still populate correctly into the calendar.
Again, Thank you SO MUCH for pointing me in the right direction!!!!

Functions Used:

=TRANSPOSE(XLOOKUP(A$9,'Master List Program'!$G$4:$G$107,'Master List Program'!$C$4:$F$107,"NONE"))

• I transposed in order for the function to populate vertically rather than horizontally.
• This function is repeated every other column starting with B, I used the date column in the Master List Program as the lookup array, and the columns labeled as “bill name” in the Master List Program sheet as the return array.
• I created another similar function in every other column starting with A, but the columns under “amount due” were utilized for the return array instead.

Relationships between Cells:

• In “June Expense” A9 and all of the dates are based on G4.
• Xlookup uses the cells with dates in them for the look up value [in cell B10, formula is entered with the lookup value being A9]
• I based the dates in the master list program on the cells labeled as month and year in the A and B columns.
• I connected the year and month cells in master list program to the year and month cells in June Expense. This way, when I change the month in June expense, the dates in the table are also adjusted.