I am trying to create a formula that does the following:
Cell e3 is a drop down list with appro. 14 entries. I want cell F# to return
a value based on the entry i select in E3. eg. If I choose Mega 423 from the
drop down list i want 0.8 to appear in F3.
The same will apply for E4 as well, but I may choose Primus 220 which has to
return a result of 1.2. Can anyone advise.

You can do this with the LOOKUP function. You will need a table with the
values that are in the drop down list and their corresponding values. Here's
an example:

Put the drop down list items in column X (i.e. in X1 enter Mega 423, in X2
enter Primus 220, etc.)

In column Y, list the corresponding values for each (i.e. in Y1 enter 0.8,
in Y2 enter 1.2, etc.)

Assuming you have a total of 14 lines of data, you would then enter this
formula in cell F3: =lookup(E3,X1:X14,Y1:Y14)

One caution: The lookup function expects the items in column X to be in
alphabetical order. This may be a different order than in the drop down
list, but that doesn't matter, the two are not linked.

I think that should do it.

Henry

"Smarty" wrote:

I am trying to create a formula that does the following:
Cell e3 is a drop down list with appro. 14 entries. I want cell F# to return
a value based on the entry i select in E3. eg. If I choose Mega 423 from the
drop down list i want 0.8 to appear in F3.
The same will apply for E4 as well, but I may choose Primus 220 which has to
return a result of 1.2. Can anyone advise.

Thanks Henry,

That seems to work OK.

"Henry" wrote:

You can do this with the LOOKUP function. You will need a table with the
values that are in the drop down list and their corresponding values. Here's
an example:

Put the drop down list items in column X (i.e. in X1 enter Mega 423, in X2
enter Primus 220, etc.)

In column Y, list the corresponding values for each (i.e. in Y1 enter 0.8,
in Y2 enter 1.2, etc.)

Assuming you have a total of 14 lines of data, you would then enter this
formula in cell F3: =lookup(E3,X1:X14,Y1:Y14)

One caution: The lookup function expects the items in column X to be in
alphabetical order. This may be a different order than in the drop down
list, but that doesn't matter, the two are not linked.

I think that should do it.

Henry

"Smarty" wrote:

I am trying to create a formula that does the following:
Cell e3 is a drop down list with appro. 14 entries. I want cell F# to return
a value based on the entry i select in E3. eg. If I choose Mega 423 from the
drop down list i want 0.8 to appear in F3.
The same will apply for E4 as well, but I may choose Primus 220 which has to
return a result of 1.2. Can anyone advise.

Just one more question how can I copy this formula down about 80 rows. Every
time I do by holding the '+' on the bottom of the cell all the info in the
formula increases by 1.

Thanks

"Henry" wrote:

You can do this with the LOOKUP function. You will need a table with the
values that are in the drop down list and their corresponding values. Here's
an example:

Put the drop down list items in column X (i.e. in X1 enter Mega 423, in X2
enter Primus 220, etc.)

In column Y, list the corresponding values for each (i.e. in Y1 enter 0.8,
in Y2 enter 1.2, etc.)

Assuming you have a total of 14 lines of data, you would then enter this
formula in cell F3: =lookup(E3,X1:X14,Y1:Y14)

One caution: The lookup function expects the items in column X to be in
alphabetical order. This may be a different order than in the drop down
list, but that doesn't matter, the two are not linked.

I think that should do it.

Henry

"Smarty" wrote:

I am trying to create a formula that does the following:
Cell e3 is a drop down list with appro. 14 entries. I want cell F# to return
a value based on the entry i select in E3. eg. If I choose Mega 423 from the
drop down list i want 0.8 to appear in F3.
The same will apply for E4 as well, but I may choose Primus 220 which has to
return a result of 1.2. Can anyone advise.

Thanks. Can you help me once more?
How do i copy the formula down about 80 rows?

"Henry" wrote:

You can do this with the LOOKUP function. You will need a table with the
values that are in the drop down list and their corresponding values. Here's
an example:

Put the drop down list items in column X (i.e. in X1 enter Mega 423, in X2
enter Primus 220, etc.)

In column Y, list the corresponding values for each (i.e. in Y1 enter 0.8,
in Y2 enter 1.2, etc.)

Assuming you have a total of 14 lines of data, you would then enter this
formula in cell F3: =lookup(E3,X1:X14,Y1:Y14)

One caution: The lookup function expects the items in column X to be in
alphabetical order. This may be a different order than in the drop down
list, but that doesn't matter, the two are not linked.

I think that should do it.

Henry

"Smarty" wrote:

I am trying to create a formula that does the following:
Cell e3 is a drop down list with appro. 14 entries. I want cell F# to return
a value based on the entry i select in E3. eg. If I choose Mega 423 from the
drop down list i want 0.8 to appear in F3.
The same will apply for E4 as well, but I may choose Primus 220 which has to
return a result of 1.2. Can anyone advise.

The references in the formula are called "relative" because they are relative
to the cell that the formula is sitting in. To keep the copy function from
changing cell references, you have to make the reference "absolute".

You do this by putting a dollar sign ($) in front of each part of the
reference that you want to remain unchanged when you copy the formula. So,
assuming you want the formula in cell F3 to stay exactly the same when you
copy it, the formula needs to look like this:

=lookup($E$3,$X$1:$X$14,$Y$1:$Y$14)

Whatever immediately follows the dollar sign doesn't change when you copy
it. You can mix and match absolute and relative references in the same
formula or even in the same reference. For example, $E3 means that when you
copy the formula, the column reference "E" wouldn't change, but the row
reference "3" would.

Handy stuff.

Henry

"Smarty" wrote:

Just one more question how can I copy this formula down about 80 rows. Every
time I do by holding the '+' on the bottom of the cell all the info in the
formula increases by 1.

Thanks

"Henry" wrote:

You can do this with the LOOKUP function. You will need a table with the
values that are in the drop down list and their corresponding values. Here's
an example:

Put the drop down list items in column X (i.e. in X1 enter Mega 423, in X2
enter Primus 220, etc.)

In column Y, list the corresponding values for each (i.e. in Y1 enter 0.8,
in Y2 enter 1.2, etc.)

Assuming you have a total of 14 lines of data, you would then enter this
formula in cell F3: =lookup(E3,X1:X14,Y1:Y14)

One caution: The lookup function expects the items in column X to be in
alphabetical order. This may be a different order than in the drop down
list, but that doesn't matter, the two are not linked.

I think that should do it.

Henry

"Smarty" wrote:

I am trying to create a formula that does the following:
Cell e3 is a drop down list with appro. 14 entries. I want cell F# to return
a value based on the entry i select in E3. eg. If I choose Mega 423 from the
drop down list i want 0.8 to appear in F3.
The same will apply for E4 as well, but I may choose Primus 220 which has to
return a result of 1.2. Can anyone advise.