Formula Help re: Recurring dates

**alexi82** · 05-17-2011, 02:34 AM

Hi There,

Hoping someone can help. I'll give you what I am trying to do first and what i have tried in excel.

I do a running sheet budget, and day of the year is listed down the left e.g. Tues 17th May, Wed 18th May etc. I then separate these using borders into each pay week, and list what bills are due that week and what income is in that week, giving a balance remaining.

Lets say that Bill A comes in on the 1st of every month, and Bill B on the 10th of every month. I want to make sure that every time the 1st is listed, it looks up the bill due on that day and displays it...

what i tried was putting a single digit number in the column next to the date to represent the day...e.g. 1, 2, 3, 4. I then tried an IF statment saying IF this cell was this number, then display Bill 1. I couldnt figure out how to do it for all bills and all cells in that column..

any ideas?

thanks!!

**arthurbr** · 05-17-2011, 03:51 AM

Hi and welcome to the board

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**alexi82** · 05-17-2011, 05:25 AM

Thanks for the advice...i have attached a file.

So the idea is, the highlighted yellow dates are bills that will come up on the same date of every month. I don't want to have to put them in every month as I tend to miss them. So I am hoping to have a list of the monthly bills, and a date on which they occur, and have them automatically put into the cell based on the row it is in and corresponding date...

Does that make sense?

**Cutter** · 05-17-2011, 09:39 AM

Try the attached

**alexi82** · 05-17-2011, 08:44 PM

thanks heaps for that!! it seems to work well however...

If i add more bills to the first month, it doesnt pick them up below when i drag the formula down...i can't figure out where the range is in that formula...

can you assist?

thanks

**alexi82** · 05-17-2011, 08:59 PM

all good! i figured out it was pointing to a named range and figured out how to add new ones...

thanks heaps for your help you guys are legends!

**Cutter** · 05-17-2011, 09:00 PM

I have changed the range from static to dynamic for you.
The named range is currently O5:Q9 (in the attached). If you add another bill it will expand another row.