Genius? Nope. But I appreciate the feedback :)
--
** John C **

"charliedog" wrote:

John, Disregard my previous note, it was my error when I changed the
reference cells. It's working great. You are a GENIUS! Many thanks.

"John C" wrote:

Once solution, would be to set up a little differently. The only thing you
need to do on the Sugar tab is name the 2 tables. Highlight the selected
region for each. Assuming your male data starts with age 5 in A2, and the 14
in D11 (for age 14 heavy intake). So highlight from A2:D11 and name it
SugarM. Do the same for the Female table and call it SugarF.
Then, your formula would be:
=IF(OR(C2="",B10=""),"",IF(OR(C3<5,C314,B10="No") ,0,VLOOKUP(C3,INDIRECT("Sugar"&LEFT(C2,1)),INDEX(M ATCH(B10,Sugar!$A$1:$D$1,0),0),FALSE)))

Now, that being said, I noticed your 2 tables were rather small, and very
linear. You have a age range of 10, and a base line for each age/sugart
intake level. They go up 1 for every year, so, if this is accurate table
(vice a sample table), you could even do away with the table. If that
interests you, let me know, I can work up the formula.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"charliedog" wrote:

Ok, heres a simplified version of what I'm trying to do... At the top of the
worksheet the user enters the name of the subject in C1, selects gender from
a drop-down in C2, then selects age from a drop-down in C3. They then answer
a serious of questions (answers selected from drop-downs) and number values
are assigned and totaled based on those answers (I've got that part). Here's
where I'm stuck: when they get to the question of "Sugar Intake" (A10), they
select from a drop-down in B10 (choices are No, Mild, Moderate, Heavy).

Sheet 1 is as follows:
--------------------------------------------
Name: John Smith
Gender: Male
Age: 7

Academics 3.5-3.9 50

Activity High 15

Sugar Intake Mild 12
-----------------------------------------------------

Once they have selected the level of sugar intake, a number will appear in
C10 based on the formula in C10, which currently looks like this:
=IF(C2="Male",INDEX(Sugar!$B$5:D$14,MATCH(C3,Sugar !$A$5:$A$14,0),MATCH(B11,Sugar!$B$3:$D$3,0)))

This formula works so far and brings the correct number into C10 based on
the chart on the "Sugar" tab, which has a specific number assigned based on
age, gender, and level of sugar intake.

Sugar (Sheet 2):
--------------------------------------------------
Age/Sex Mild Moderate Heavy
Male
5 10 12 15
6 11 13 16
7 12 14 17
8 13 15 18
9 14 16 19
10 15 17 20
11 16 18 21
12 17 19 22
13 18 20 23
14 19 21 24

Female
5 9 10 14
6 10 11 15
7 11 12 16
8 12 13 17
9 13 14 18
10 14 15 19
11 15 16 20
12 16 17 21
13 17 18 22
14 18 19 23
--------------------------------------------
I now need to fix the formula so that if "Female" is selected, it will pull
in the correct figure from the Sugar tab based on the female data (B17:D26).
Also, if "no" is selected on sheet1, B10, or if the age selected is out of
the age range covered by the Sugar table, I need "0" to display in C10
instead of "#N/A", which is what appears now.

Should the formula use VLOOKUP instead of INDEX? Any advice on the best way
to handle this would be greatly appreciated. I am a novice, learning on the
fly, and don't understand the syntax of these formulas very well. Thanks in
advance for any and all assistance.