Telling Excel where to get data without writing a whole bunch of IF statements

[misterhanky]

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

 

[Roger Govier]

One way
Make a Table in say A2:B10 with 2004 in A2 and C13 in B2, 2005 in A3 and D13
in B3 etc.
Then use a Vlookup within your formula to pick up the cell to use
=VLOOKUP(A1,$A$2:$B$10,2,0)

 

[Roger Govier]

Another way would be to use an offset in your formula where instaed of
referring to C13, use
=OFFSET(C13,,,(year_chosen-2004)
where the cell used would be offset the correct number of columns from C13
according to the year chosen

 

[misterhanky]

Roger--

HLOOKUP did it. I am truly, truly relieved. Thank you.

 

[Biff]

Hi!

All that will do is return a text string, C13.

=INDIRECT(VLOOKUP(A1,$A$2:$B$10,2,0))

Will return the value in cell C13.

The OP needs to supply more info like the actual formula being used.

Biff