Formula to select a cell when a condition is met etc

[pedro-egoli]

On another thread http://www.mrexcel.com/board2/viewtopic.php?t=146667

I sought assistance with a formula to calculate Australian Tax on Income, and have this side of things working well, with the two formulae provided.

As part of the worksheet I want to start off all the calculations that lead to the tax calculation at the age I input for initial year.
This way anyone can input their age and have the spreadsheet start calculations from their individual age.

Details of cells involved are
A4 Opening Balance (input)
B5 Opening Age (input)
B8:B53 Ages running from age 55:100
D8:D53 Cells for Opening Balance
N.B. Opening balance in column D will be A4 initially but in second year and beyond it will be the closing balance for the year before.
G8:G53 Is the closing balance made up by that year's Opening balance+Income-Drawings.

What I need is a formula that will input the figure of A4 in the cell adjacent to the year which matches the age input into B5.
I then need the following years Opening balance (from cell B8:b53) to be that of the initial year's Closing balance ; this to continue to th cell B53.

eg. If age 57 is input in B5 I want balance in A4 to appear in cell D10 and in cell cell D11 i want it to be the closing balance of previous year shown in G10. This transfer of balances to continue to row 53.

It would neaten up the sheet if the rows above the initial opening balance (rows 8 and 9) to be blank.

All suggestions welcome thanks
Pedro

 

[pedro-egoli]

Thanks again Fairwinds, it works well.

2006 is the financial year (1/7/05 to 30/6/06) that this is deemed to start.

Many thanks
Pedro