Wow. This is tough to describe. I'm writing a rudimentary financial
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
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
I hope I've adequately explained the problem. Any help?