M

#### misterhanky

spreadsheet that produces a share value based on Earnings Per Share in

a given year.

As it stands now, the spreadsheet and formulas are simple. The

"Valuation" page imports Earnings Per Share from another sheet in the

workbook, and everything on the "Valuation" page is hard-coded to look

in H13 for 2009 Earnings Per Share.

G13 has 2008 Earnings, H13 has 2009 Earnings, I13 has 2010 Earnings,

and so on.

The drawback with this approach is that the spreadsheet can only

calculate valuation based on 2009 earnings. If I want to use 2008, or

2010, or any other year, I have to rewrite a bunch of formulas. (I know

that I could Find->Replace, but this won't help the people in my office

who know even less than I know)

I realize that I could fix this by using a _whole_bunch_ of IF

statements--

For example (in English), I could tell the formula that IF A2 is 2004,

use the data in C13, IF A2 is 2005, use the data in D13, IF A2 is 2006,

use the data in E13...IF A2 is 2013, use the data in N13, and so on.

I have found references that explain how to nest IF statements and

combine others that have reached the nesting limit. Of course, this

seems like a reeaally stupid way to solve the problem.

In other words, I need to tell Excel that "If the user puts 2010 in

cell A2, use the figure in I13. For every number less, go left one

cell. For every number greater, go right one cell. Spit out the value

in D54."

I hope I've adequately explained the problem. Any help?