Hi, I'm relatively new to excel and would appreciate any help with th following problem. I'm working on a pensions spreadsheet and need to work out income a retirement over a set period. I have tables which have all the require formulas and a manual goal seek works fine if I enter the set cell o the corresponding period entered by the customer. Unfortunately th period can change, i.e. some may want the income for 15 years, others 2 years or whatever. I realise to automate this I need a macro but I don't know how to d this with a variable set cell. I've tried with vlookup but that doesn' work. I've worked out a formula to give me the location of the set cel 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

You might not need to use Goal Seek at all. It depends on what your variables are. I suggest that you post the URL of an example Excel file that you uploaded to a file-sharing website. The following is a list of some free file-sharing websites; or use your own. Box.Net: http://www.box.net/files Windows Live Skydrive: http://skydrive.live.com MediaFire: http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com RapidShare: http://www.rapidshare.com Assuming you are familiar with writing VBA macros, try the following code fragment. I assume that data starts in row 1, and the number of years is 2 or more. Dim lastRow As Long lastRow = Range("A1").End(xlDown).Row Range("C" & lastRow).GoalSeek Goal:=0, ChangingCell:=Range("B1") ----- original message -----