"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
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha 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
...

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