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 -- raith99

"raith99" <> wrote: > 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 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 "raith99" <> wrote: > 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 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 ----- "raith99" <> wrote in message news:... > > 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! > > > > > -- > raith99