As Today's Date Changes, After 7 Days, The Range of The Lookup Changes

Ok it's pretty difficult to describe what I need help with but I'll try my best.

Currently on Excel, I have a sheet that contains my Income and Outgoings for each week until I get paid the following week (as I get paid weekly).

What I have done is set it up so that I have conditional formatting on each WEEK DATE cell, so that they change colour to grey when that date is equal or less than TODAY'S DATE so that I know which column I am working from.

Next I have a cell that has the formula "=MIN(D23:AM23)" to find what the lowest amount will ever be across the whole year however the lowest amount is nearly always the current week so I want "=MIN(D23:AM23)" to change to "=MIN(E23:AM23)" once the following week commences.

Which I believe is known as a dynamic range. (from what I've read)

And therefore I need the range to shrink after each week,

Good Luck guys,

and thanks!

Untitled.png

This is how it looks at the moment (Ignore the 29th)

As you can see at the bottom highlighted in green, is how much money I would have left after all income and outgoings.

this is where the =Min( function is used to find the lowest number so that I don't overspend later on down the line.

However this means I need to manually change it after the 7th as I'll have more than £73.86 to spend

Is there a formula for moving past weeks onto another sheet so that they can still be referenced? Because I think that could work too.

I can't just delete them since the following week uses the past weeks final balance and causes #REF to appear if the past week is deleted.

Well, if you look at mine, on the upper left hand corner, there's the balance at the beginning of the week. That's all I have to update when I delete the row. My totals on the right side have the following formula:

Please Login or Register  to view this content.

Here's what each Cell represents:
B3 = Income or Current Balance
C3 = An extra row for Additional possible income
W3 = Total at end of week after all charges go through
Y2 = Total left over from last week after all charges went through.

So I add my income, last weeks balance remaining, this weeks balance remaining and any extra income to come up with what's left at the end of each week.

The ISNUMBER again is checking to see if the cell above is actually a number or not, if it's the topmost row, it will see the Heading as the next row up and not calculate it in the total, so that you don't get the #REF error's showing up in the sheet. They DO show up in the formula, but they aren't calculated because ISNUMBNER will just return 0. So I delete the entire row and the formula fixes itself without me having to change anything other than the current balance. I simply log into my bank on Friday, and update the topmost income number with my current balance from my bank. I could take it a step further and add a cell somewhere to be the current balance, and then add an income formula to show the following:

B3=IF(ISNUMBER(B2),640, >CURRENT BALANCE CELL<)

This would enter 640 if it's not the topmost cell (which is my income for each week) if it's the topmost cell, it would enter the current balance if it's the topmost cell. Obviously copy/paste to the cells below to fill in the formula to the rest. You could also take it a step further and set the 640 in another cell by itself, and replace that reference with your INCOME cell.

Granted, this only works with having a single bank account and a single source of income, if you have multiples though, you could just add another column in for that income and add that into the rest of your formulas. Not too complex there.

But using ISNUMBER to check if it's not the topmost cell helps in a lot of cases when all you need is to know the budget going forward...

I don't know of a proper way to move the old data to another sheet, I would say that would probably be VB Code at that point... cause it involves macroing and such... You could reference the different sheet in a similar way by using some kind of formula like... CURRENTWEEKINCOME+IF(ISNUMBER(PREVIOUSWEEKINCOME),PREVIOUSWEEKINCOME,OTHERSHEETPREVIOUSWEEK)) which would still update dynamically based on where it is... but the act of moving to another sheet and putting it in the right spot and all that... that's a little complex...

Hope that helps a little.

wait, I just want a range to change per week.

so from 30/04/2015 to the 07/05/2015 it should change from "=MIN(C23:AL23)" to "=MIN(D23:AL23)" once it actually is 07/05/2015. Then once it's 14/05/2015 it would change to "=MIN(E23:AL23)"

Is there not a formula to do this?
As I'd like to keep past payments incase I need it in the future.

Yea... we got a bit sidetracked I think. :/

Here, I've attached.

If I understand you correctly:

Please Login or Register  to view this content.

This seems to have worked as I wanted, thank you jhren.

If you don't mind, could you explain how this formula works?

Thanks again

See if this works for you...

Limiting Week: =INDEX(C6:AL6,0,MATCH(B29,C23:AL23,0))
Remaining Weeks: =MATCH(B29,C23:AL23,0)-MATCH(TODAY(),C6:AL6,1)

Thanks man, this works a treat (I like the Limiting Week formula that you added too).

I owe you a couple jhren