Modifying cells based on different worksheet value

[mondeoman]

I have a workbook that allows me to calculate the balances needed to fund my mother's care home fees. In basic terms there is a worksheet that shows her income for different sources and her expenses. Her income is usually uplifted by inflation each April. My problem is that if I change the referenced worksheet all months are changed from Jan to Dec where I only want the Months from Apr to Dec to show the uprated income. The sam applies to fees although the change takes place in September.

I have tried to put an 'if' statement in the worksheet named year (in the attached example held on Box.net - link below)and clearly what I get is the months from Mar onwards picking up the new value in the Income cell and changing the old values to false whereas I want to keep the old values. So in the attached example for instance the new Pension value is 956 which the formula picks up but rather than change the old values to false I want them left with the old value which was 935. The statement I am using is: =IF(B5>3,Income!$B$4)

Can anyone help please?

 

[byundt]

In cell B8, try this formula (which may be copied across):
=IF(B5>4,956,935)
That formula will return 935 from January through April, and 956 afterwards.


Another approach you may find more flexible is to put 935 in B8, and then the following formula in cell C8 (copied across):
=B8
This will put 935 in all cells on row 8. But when you put 956 in cell F8, you will have 935 for January through April and 956 for May through December. The benefit of this approach is that you can put the uplifted income in the appropriate cell when you learn the amount. You can also use the same formula (copying the column to the left), for all other rows.

 

[mondeoman]

Thanks for the idea and I agree it is one way of doing it. However, the next year it is not going to be 935 it will be 956. DOn't forget that the cells here are picking up whatever is in the cell on the income sheet so lets say when I get to next year and I want to enter the new figure for the Income sheet the Year sheet should pick that new figure up only for the appropriate months - in this case April onwards.

 

[SkipVought]

hi,

You must have a table of rates and dates and then use the INDEX() & MATCH() functions to lookup the date with a Match Type of 1 or -1 depending on how your rate table is sorted, and return the proper rate based on that date.
[tt]
Rate Date
900 4/1/2010
915 4/1/2011
945 4/1/2012
956 4/1/2013
[/tt]
and the formula to return the rate
[tt]
=INDEX(Rate,MATCH(F3,Date,1),1)
[/tt]
where F3 contains a date in your main table and using Named Ranges.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

If use the second formula I suggested (the one that copies the cell in adjacent column to the left), you can seed the next year's sheet by copying the values from December and doing a Paste Special...Values in the January column of the following year.

 

[mondeoman]

Thanks SkipVought and byundt for you inputs but I don't think I can be explaining myself properly. Putting either of the above 2 ideas into the spreadsheet is like hard coding the answers. What I want is something that is dynamic so that I only update the one relevant field in the income column. For example let us just take the Pension income for 2013 - i.e. B4. I use this worksheet in the full version of my spreadsheet as the base for all other worksheets. Each year I add a new worksheet to show the uplifted amounts. So in 2012 the pension was 935 and this ran through until 31 Mar 2013. the 935 for a 2012 tab was picked up from the value in B4 of the Income worksheet. What should happen is that when completing the 2013 sheet the 935 from 2012 remains unchanged but the remaining months - i.e. Apr onwards reflect the new value in B4. Then once 2013 is finished I add a new worksheet for 2014 and the process starts over again. I hope this isn't too confusing. I have changed the example to show these aspects in the reply. If you look at the partly completed 2014 the same formula picks up the 2013 rate but the 956 is hard coded and I would have make manual changes every year which is what I am trying to avoid.

In english rather than code I want a formula, function or macro that will do this:

If month => Apr then replace with Income!$B$4 Else leave unchanged

This logic then should allow the old and new sheets to keep the last years value, where appropriate, but uprate the months from APr onwards.

 

[SkipVought]

So your Pension Income is from column B. Then you should ALSO have a column that reflects the DATE associated with an particlular income value. THAT is the date and column B is the return amount, using the formula I posted (references adjusted accordingly)

Each year I add a new worksheet to show the uplifted amounts

That's a shoot-yourself-in-the-foot decision! You are making the task many MANY times more difficult than it need be! A proper structure has ONE table for each relation (like income). CHopping you your data into different fields/sheets/workbooks will multipy your woes and magnify your sorrows. If you had ONE table representing income, (I assumed that you did) with ONE column for Pension income and ONE column for date, your process would be much simpler that it is now.

It would also be better if you owere to post a sample of data that you are trying to manage, than describing it in prose, like the little rate table is posted as an example.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mondeoman]

There is an attachment to my post with a simple example (Not the full spreadsheet)of what I am trying to do. It is called Annual_Uplift. If this is not accessible or seen please advise.

 

[SkipVought]

My external access is restricted by our IT policies.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]