I am producing a cashflow. It is over five years, I have established that
average contract values for 2006, 2007, 2008 2009 and 2010 will be 800k,
1200k, 2000k, 2500k and 3000k respectively. I want the cell with the contract
value to change automatically if a project start date is entered, eg if a
project starts in March 2007 its value will be 1200k.

Can anyone help me?

Here are some ideas:
a) =CHOOSE(A10-2000,1200,1500,1600........)
b)=IF(A11=2006,2000,IF(A11=2007,2000,IF(A11=2008,2 500,0)))
come back with specific question
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Struggling of Essex" wrote in
message ...
I am producing a cashflow. It is over five years, I have established that
average contract values for 2006, 2007, 2008 2009 and 2010 will be 800k,
1200k, 2000k, 2500k and 3000k respectively. I want the cell with the
contract
value to change automatically if a project start date is entered, eg if a
project starts in March 2007 its value will be 1200k.

Can anyone help me?

Try this:
=LOOKUP(YEAR(A1),{2006,2007,2008,2009,2010},{800,1 200,2000,2500,3000})

if your looking to add the "K" at the end of the value then add this to
the end of the formula

&"K"

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=496732

Many thanks but to be more specific

The cells within column D2:D60 have specific commencment dates for a project
e.g. project 1 commences 13 March 2007, therefore D2 has this date, I want E2
to show the value 800,000 automatically. Similarly D3' commencment date is 11
July 2008, therefore I would like the adjacent e3 to show 1,200,000. I am
showing values for 2007, 2008, 2009, 2010 and 2011.

Project values for projects commencing in 2007 will be 800,000
Project values for projects commencing in 2008 will be 1,200,000
Project values for projects commencing in 2009 will be 2,000,000
Project values for projects commencing in 2010 will be 2,500,000
Project values for projects commencing in 2011 will be 3,000,000

I hope you can help me

Thanks.

"Bernard Liengme" wrote:

Here are some ideas:
a) =CHOOSE(A10-2000,1200,1500,1600........)
b)=IF(A11=2006,2000,IF(A11=2007,2000,IF(A11=2008,2 500,0)))
come back with specific question
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Struggling of Essex" wrote in
message ...
I am producing a cashflow. It is over five years, I have established that
average contract values for 2006, 2007, 2008 2009 and 2010 will be 800k,
1200k, 2000k, 2500k and 3000k respectively. I want the cell with the
contract
value to change automatically if a project start date is entered, eg if a
project starts in March 2007 its value will be 1200k.

Can anyone help me?

Many thanks but to be more specific

The cells within column D2:D60 have specific commencment dates for a project
e.g. project 1 commences 13 March 2007, therefore D2 has this date, I want E2
to show the value 800,000 automatically. Similarly D3' commencment date is 11
July 2008, therefore I would like the adjacent e3 to show 1,200,000. I am
showing values for 2007, 2008, 2009, 2010 and 2011.

Project values for projects commencing in 2007 will be 800,000
Project values for projects commencing in 2008 will be 1,200,000
Project values for projects commencing in 2009 will be 2,000,000
Project values for projects commencing in 2010 will be 2,500,000
Project values for projects commencing in 2011 will be 3,000,000

I hope you can help me


"pinmaster" wrote:


Try this:
=LOOKUP(YEAR(A1),{2006,2007,2008,2009,2010},{800,1 200,2000,2500,3000})

if your looking to add the "K" at the end of the value then add this to
the end of the formula

&"K"

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=496732

Try this then:

=IF(D2="","",LOOKUP(YEAR(D2),{2006,2007,2008,2009, 2010},{800000,1200000,2000000,2500000,3000000}))

copied down

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=496732

On Thu, 29 Dec 2005 10:31:02 -0800, "Struggling of Essex"
wrote:

I am producing a cashflow. It is over five years, I have established that
average contract values for 2006, 2007, 2008 2009 and 2010 will be 800k,
1200k, 2000k, 2500k and 3000k respectively. I want the cell with the contract
value to change automatically if a project start date is entered, eg if a
project starts in March 2007 its value will be 1200k.

Can anyone help me?

Set up a table some place:

2006 $800k
2007 $1200k
2008 $2000k
2009 $2500k
2010 $3000k


Use this formula:

=VLOOKUP(YEAR(Start_Date),Table,2)

Start_date refers to a cell containing your start date as a normal excel date.

Table refers to the range where the above table is located.
--ron