I think in a nutshell that is about it.  Just kidding.

Someone will enter a number is cell A1, then a number in B1, then a
name in C1.  I have two charts spanning from O3:Y14 and O18:Y29.

The formula would first need to pick which chart to use based on C1. 
From there, it would need to cross reference the number on the chart
based on A1,B1.

So what will be entered is.  (in column A it would be 2-12, Column B
would be 3-12, Column C would either be F or T, Column D would either
be P, 19, or if left blank would be the number referenced in the
chart)

5   10     F    PL   


(say F is the first chart listed.)  With the graph below (i shortened
it for ease), the answer would be 8.56 and this would then need to be
multiplied by 1.6 if P is selected in column D, 1.18 if 19 is selected
for Column D, and if nothing is selected in Column D then
automatically the answer of 8.56 would be there.  i would put this
answer into column E as more calculations would need to be applied
onto it later.

	3	4	5	6	7	8	9	10
2	2.63	2.77	2.9	3.04	3.18	3.33	3.45	3.59
3	3.86	4.05	4.25	4.45	4.65	4.85	5.05	5.25
4	5.06	5.32	5.58	5.84	6.11	6.37	6.63	6.9
5	6.28	6.6	6.92	7.24	7.58	7.89	8.22	8.56
6	7.5	7.87	8.26	8.64	9.04	9.41	9.81	10.21
7	8.71	9.15	9.6	10.04	10.51	10.93	11.4	11.87
8	9.93	10.42	10.94	11.44	11.97	12.45	12.99	13.52
9	11.14	11.7	12.28	12.84	13.44	13.97	14.58	15.18
10	12.36	12.97	13.62	14.24	14.9	15.49	16.17	16.83
11	13.57	14.25	14.96	15.64	16.37	17.01	17.76	18.49
12	14.79	15.52	16.3	17.04	17.83	18.53	19.35	20.14

---

Request for Question Clarification by maniac-ga on 30 Sep 2006 18:00 PDT

Hello Thewenanator,

I believe I have a solution for your question however I am not certain
of a few points:

[1] You indicate that C1 is a "name". Do you mean that C1 has a
defined name such as created with the menu
  Insert -> Name -> Define
or some other reference (e.g., a value used to look up a reference)?

[2] You indicate the multipiers based on the D1 value are
  P - 1.60
  19 - 1.18
  blank - 1.00

Yet the example has PL as the value in D1.

[3] I assume you want a formula in E1 that does the appropriate
processing. Is that correct?

Thanks.
  --Maniac

---

Clarification of Question by thewenanator-ga on 02 Oct 2006 14:30 PDT

Hi Maniac,

C1 would be 'text'.  I should have been a bit more clear on that. 
They are either going to use the letter F, or T.  F meaning that the
calculations should be for Chart one, T would have the calculations
based on chart 2.

I put PL, but it should have read simply "P".  The only thing i might
be concerned about would be if they accidentally type more of the word
instead of the abbreviated "P"... I know that it will probably show an
error if someone entered a variable that isn't recognized so don't
worry about this.  I will put a legend into the chart to guide them.

E1 would have the appropriate formula/processing, thank you.

Hello Thewenanator,

Thanks for the clarification of the question.

The formula (two version) to paste into cell E1 is included at the end
of this answer. The first formula assumes that chart one (indexed by
"F") starts at cell A3 (or row 3, column 1) and chart two (indexed by
"T") starts at cell A17 (or row 17, column 1). I assume the values to
look up are in different locations. I'll explain the values to adjust
to correct the formula for your situation (or use the second version).

Let me explain the formula in detail as well as the methods used to
determine the correct operation. The functions I used are:
  IF
  INDIRECT
  TEXT
if you type those phrases into the Microsoft Excel help, you can find
additional information related to those functions. I also used the
string operator & to concatenate two strings together.

First, based on your description, multiple values need to be looked up
for the multiplier. I used nested IF functions to do this as follows:
  IF(D1="P",1.6,IF(D1=19,1.18,1))
This compares D1 to the string value "P" and if true, returns 1.6. If
D1 is not "P", the next check is for the number 19 (not the string 19)
and if true, returns 1.18. If both are false, the value 1 is returned.
If you want to explicitly check for blank, revise this to something
like:
  IF(D1="P",1.6,IF(D1=19,1.18,IF(D1=" ",1,0)))
which adds the explicit check & returns zero if none are matched.
Alternatively, you could put 1/0 in place of 0 to generate an error
value (#div/0!) if desired.

Second, based on your description, we need to use another IF function
to determine which table (or chart) of data to use. The construct is
something like this:
  IF(C1="F",[formula for chart F], [formula for chart T])
Again, if you need to explicitly check for "T" (and not any other
value), this can be rewritten like
  IF(C1="F",[formula for chart F], IF(C1="T",[formula for chart T],0))
You can also generate an error like described previously if desired as well.

Third, based on your description, we need to look up a value based on
the data from the two cells (A1 and B1). To do this, I used the
"INDIRECT" function - one I find rarely used by Excel users. There are
several good online references for this function as well - search
online with a phrase like
  Excel indirect
for those sites.

Since I indicated that F started at cell A3 (or row 3, column 1), the
formula to look up the value is:
  INDIRECT(TEXT(2+A1,"R#")&TEXT(B1-1,"C#"),FALSE)
This is a little complicated, so let me explain the steps involved.

INDIRECT takes a cell reference in A1 format or R1C1 (row / column)
format. I am using the latter, so the second parameter must be False.

The first TEXT function call generates the row reference (R5) using
the two plus the value in A1 and the format R# (the letter R followed
by the numeric value). The second TEXT function call generates the
column reference (C9) using the value in B1 minus one and the format
C# (the letter C followed by the numeric value). The two strings are
concatenated to produce the string value R5C9 used to look up the
value 8.56 as described in your question.

In a similar manner, the values in "T" are referenced with the second
INDIRECT function call using row and column offsets of +16 and -1 to
compute the proper cell references.

Since I expect your values (for "F" and "T") to be in different
locations, adjust the values (+2, -1) and (+16, -1) to refer to the
proper row / column to look up the values.

As another alternative - if you have defined names for the two regions
of values ("F" and "T"), you can use a pair of functions ROW and
COLUMN to lookup the starting row & column of the defined names. I
have included another version of the formula at the end using this
method - assuming the defined names of "F" and "T" for the range of
look up values. This second version will adjust the row / column
references automatically if you add / remove rows or columns so that
"F" and "T" moves. The first version would have to be fixed manually
if you reorganized the spreadsheet in this way.

If you have any difficulty adapting either formula for your specific
situation, if any part of the answer is unclear, or you need
additional information on this topic, please make a clarification
request. I would be glad to help you further.

Good luck with your work.

  --Maniac

The formula is split into several lines so to make it more readable -
combine into a single formula when you copy & paste into your cell at
E1.

=IF(D1="P",1.6,IF(D1=19,1.18,1))*
  IF(C1="F",INDIRECT(TEXT(2+A1,"R#")&TEXT(B1-1,"C#"),FALSE),
  INDIRECT(TEXT(16+A1,"R#")&TEXT(B1-1,"C#"),FALSE))

The second alternative, using names F and T to refer to the two ranges
of look up values. This also includes an example of an error generated
if the value isn't set properly in cell D1.

=IF(D1="P",1.6,IF(D1=19,1.18,IF(D1=" ",1,1/0)))*
  IF(C1="F",
  INDIRECT(TEXT(A1+ROW(F)-1,"R#")&TEXT(B1+COLUMN(F)-2,"C#"),FALSE),
  INDIRECT(TEXT(A1+ROW(T)-1,"R#")&TEXT(B1+COLUMN(T)-2,"C#"),FALSE))

Excellent, thank you very much.  I got it to work and i understand how
the chart pulls off the numbers (so if i need to move my chart or add
lines i understand how to make that work).