Help on stopping cells from changing

[debtless]

Hi
I have attached a file that I am setting up for my kids allowances. I am using an IF statement to auto enter the allowance amount every friday.
The problem is that when I want to change the allowance amount it changes all entries. I just want it to change future entries.
I know this is a simple spreadsheet but I am new to excel and just learning as I go.
Any help would be appreciated.
Thanks in advance for the help.

[TMS]

To be honest, I think the only thing you can do is Copy and Paste Special Values on the payments made prior to the increase in allowance.

You could create a macro to do that, perhaps.

Regards

[teylyn]

Hello,

You will need to build a table with allowances that refer to a time frame, then look up the corresponding allowance for that time frame for each child. In the attached example, I have created a table with separate allowance columns for Caitlin and Liam. The leftmost column is the date where the allowance changes.

In columns B and E, the formula now does not refer to the fixed value in row 4, but rather looks up what allowance applies for the date in columns A or E respectively. The formula is in B6

=IF(A6<=TODAY(),INDEX($H$2:$J$12,MATCH(A6,$H$2:$H$12,1),MATCH(A$2,$H$2:$J$2,0)),"")

copied down the column. The same formula is then copied to column E.

Now you can set individual allowances for Caitlin and Liam. If their respective allowances do not increase on the same date, then just repeat the previous allowance amount for the person who did not get a raise. For example: on 1st June, the allowance for Liam is raised to 5.70, but Caitlin's allowance stays at 6.00. So there is a new row in the allowance table, listing the new date, but repeating the 6.00 amount for Caitlin, but Liam's column shows a new amount.

Does that make sense?

Your children do not have to have the same allowance each. The allowance can change at any defined date and will not upset the book keeping for past payments. Allowances can be raised for each child individually.

Run it past your kids. If they are as jealously monitoring each others' privileges as mine are, and if they agree that this method might work, we may have a major break-through

cheers, teylyn

[teylyn]

Forgot to say: With this method you also have a documented history of allowances and their raises, so you have an audit trail when the younger kid claims a raise earlier than is due to their age bracket. You can then go back to the table and prove that the older child only got to $xx when they were xx years old and that it is NOT "unfair" that the younger child is not receiving funds worthy of a CEO of a major corporation.

PS. I see you're very organised around the financial aspects of child rearing. If you also have a system that works well for distributing chores (and maybe one for cutting down on the answering back), please send me a PM. I'd be VERY interested.

exasperated, teylyn

[BarryTSL]

I have seperated the allowance which is a variable. Hope this works ok for you

[TMS]

@teylyn: ah, a much more sophisticated approach ;-)

[teylyn]

@TM, thanks, but it's more common sense than anything sophisticated. Especially if you have kids raging in a sibling rivalry war.

[debtless]

Thanks for the help all. @teylyn, having the answer to talking back and stopping kids from bickering would be like finding the holy grail!!

Have decided that I want to take this spread sheet to the next level and have it split their income into separate categories. Apportion a % to say long term savings, % to charity, everyday spending, etc. Try and teach the kids about saving a little better, have them set a goal about what they could aim for with long term savings.

Could also have the long term savings earn interest. HHmm I will have to work on this.

Thanks again

[teylyn]

Good luck. I'm happy to help if you want to sanity-check Excel stuff. If only children were as easy to manage as Excel ...

[debtless]

@teylyn, here is what I have come up with. Bit crude, but seems to work ok for me. I can't be bothered trying to improve the layout etc. I have locked the cells for the kids but there is no password.

The kids can see what is happening to their money, trying to reward them to save by paying interest on their long term savings but may have to increase the interest so they are more rewarded to save.