Hi,

I'm relatively new to excel and would appreciate any help with the following problem.

I'm working on a pensions spreadsheet and need to work out income at retirement over a set period. I have tables which have all the required formulas and a manual goal seek works fine if I enter the set cell of the corresponding period entered by the customer. Unfortunately the period can change, i.e. some may want the income for 15 years, others 25 years or whatever.

I realise to automate this I need a macro but I don't know how to do this with a variable set cell. I've tried with vlookup but that doesn't work. I've worked out a formula to give me the location of the set cell but again have no idea how to get this to work with a goal seek macro.

Column A has the number of years (1-30)
Column B has the annual income
Column C has the total fund value

So if required period 15 years the Set Cell would be C15, if 20 years, C20, etc.
Goal Value would be 0
Changing Cell would be B1

Hope you can help before I pull all my hair out!