I am using a combo box to fill in some standard information and I know how
they should appear in the cell, so I can copy and paste to a new cell. But If
my list has two cell how can I get that the destiby cells appears one of the
cells info only.

Example

A1 B1
Urban 1
Rural 2
I want to choose Urban from the combo box, but the information to be stored
in the destiny cell should be 1.
I know how to do it for Urban, that is the destiny will show Urban but I
want to get 1.


Please help

Alberto Vargas

Alberto,

If your numbers in column B are consecutive (1, 2, 3... no missing numbers),
you could use the combo box from the forms toolbar. It puts the index
number of the item you selected (2 for the second item, etc.). If they're
not consecutive, you'll need to get the associated number in column B from
the item selected from the first column. Here is a possible solution:

Use the combo box from the forms toolbar. Link it to an extra cell
somewhere (K15 in this example). Then in your cell where you want the
number from column B, use:

=INDEX(B1:B3,K15)

In this case, the extra cell is K15, which you could hide. It should have 2
if the second item was selected in the combo box. The INDEX formula should
get the second item in the column B range.
--
Earl Kiosterud
www.smokeylake.com

"Alberto Vargas" <Alberto wrote in message
...
I am using a combo box to fill in some standard information and I know how
they should appear in the cell, so I can copy and paste to a new cell. But
If
my list has two cell how can I get that the destiby cells appears one of
the
cells info only.

Example

A1 B1
Urban 1
Rural 2
I want to choose Urban from the combo box, but the information to be
stored
in the destiny cell should be 1.
I know how to do it for Urban, that is the destiny will show Urban but I
want to get 1.


Please help

Alberto Vargas

Earl,

Thanks for your response. It helped me to understant this but I still do not
have the response I want.

When I used the INDEX formula, it appears #VALUE error. Actually I used your
cells in the example and it did not worked either.

On regards my question, the numbers are not consecutive, so I assume that I
will need to enter a VLOOKUP formula to have the code.

I have my list in cells A1 B2 (the list is bigger but this is just an example)

Urban 20
Rural 99

In the "listFillRange" option I enter A1:B2, and my linked cell is A3.

Using the combo box I choose Rural and in A3 (linked cell) it shows Rural
Then in A4 I copied and paste the results from A3, and in A5 I used the
VLOOKup formula to finally determine the code for Rural so it shows "99"

So there is not way that when I choose as an example "Rural" in the combo
box, the linked cell will shows "99" directly?

Please let me kmow if there is not any option to do this just one in step
instead of four.

Thanks

Alberto





"Earl Kiosterud" wrote:

Alberto,

If your numbers in column B are consecutive (1, 2, 3... no missing numbers),
you could use the combo box from the forms toolbar. It puts the index
number of the item you selected (2 for the second item, etc.). If they're
not consecutive, you'll need to get the associated number in column B from
the item selected from the first column. Here is a possible solution:

Use the combo box from the forms toolbar. Link it to an extra cell
somewhere (K15 in this example). Then in your cell where you want the
number from column B, use:

=INDEX(B1:B3,K15)

In this case, the extra cell is K15, which you could hide. It should have 2
if the second item was selected in the combo box. The INDEX formula should
get the second item in the column B range.
--
Earl Kiosterud
www.smokeylake.com

"Alberto Vargas" <Alberto wrote in message
...
I am using a combo box to fill in some standard information and I know how
they should appear in the cell, so I can copy and paste to a new cell. But
If
my list has two cell how can I get that the destiby cells appears one of
the
cells info only.

Example

A1 B1
Urban 1
Rural 2
I want to choose Urban from the combo box, but the information to be
stored
in the destiny cell should be 1.
I know how to do it for Urban, that is the destiny will show Urban but I
want to get 1.


Please help

Alberto Vargas

Alberto,

Since you're using "List Fill Range" instead of "Input Range," I presume
you're using a combo box from the Controls Toolbox, and looking in the
properties list for stuff like Linked Cell and List Fill Range", rather than
just looking at format Control of the combo box (from Forms toolbar)? The
two combo boxes work differently. My example is for the combo box from the
Forms toolbar. You'll still need an intermediate cell (the linked cell of
the combo box), but we'll have to handle it differently. Which is it?
--
Earl Kiosterud
www.smokeylake.com

"Alberto Vargas" wrote in message
...
Earl,

Thanks for your response. It helped me to understant this but I still do
not
have the response I want.

When I used the INDEX formula, it appears #VALUE error. Actually I used
your
cells in the example and it did not worked either.

On regards my question, the numbers are not consecutive, so I assume that
I
will need to enter a VLOOKUP formula to have the code.

I have my list in cells A1 B2 (the list is bigger but this is just an
example)

Urban 20
Rural 99

In the "listFillRange" option I enter A1:B2, and my linked cell is A3.

Using the combo box I choose Rural and in A3 (linked cell) it shows Rural
Then in A4 I copied and paste the results from A3, and in A5 I used the
VLOOKup formula to finally determine the code for Rural so it shows "99"

So there is not way that when I choose as an example "Rural" in the combo
box, the linked cell will shows "99" directly?

Please let me kmow if there is not any option to do this just one in step
instead of four.

Thanks

Alberto





"Earl Kiosterud" wrote:

Alberto,

If your numbers in column B are consecutive (1, 2, 3... no missing
numbers),
you could use the combo box from the forms toolbar. It puts the index
number of the item you selected (2 for the second item, etc.). If
they're
not consecutive, you'll need to get the associated number in column B
from
the item selected from the first column. Here is a possible solution:

Use the combo box from the forms toolbar. Link it to an extra cell
somewhere (K15 in this example). Then in your cell where you want the
number from column B, use:

=INDEX(B1:B3,K15)

In this case, the extra cell is K15, which you could hide. It should
have 2
if the second item was selected in the combo box. The INDEX formula
should
get the second item in the column B range.
--
Earl Kiosterud
www.smokeylake.com

"Alberto Vargas" <Alberto wrote in
message
...
I am using a combo box to fill in some standard information and I know
how
they should appear in the cell, so I can copy and paste to a new cell.
But
If
my list has two cell how can I get that the destiby cells appears one
of
the
cells info only.

Example

A1 B1
Urban 1
Rural 2
I want to choose Urban from the combo box, but the information to be
stored
in the destiny cell should be 1.
I know how to do it for Urban, that is the destiny will show Urban but
I
want to get 1.


Please help

Alberto Vargas

Hi Earl,

Sorry that I did not respond early.

I am using now the combo box from the Forms toolbar and it looks great. I
have two questions:

1. What are the differences in using a combo box from the controls toolbox
and the Forms toolbar.

I have so many combo boxes in my file, like 100, so it is becoming very
heavy and it takes time the file to open. So I am thinking to switch to combo
boxes using the Forms tool bar.

2. For the combo boxes from the Forms tool bar, there is any way that I
could choose the cell that I want in my linked cell, or I still need an
intermediate cell.
So if I have Rural 99, and I choose Rural there is any way that my linked
cells shows up 99 without an intermediate cell.

Thanks for your help

Alberto



"Earl Kiosterud" wrote:

Alberto,

Since you're using "List Fill Range" instead of "Input Range," I presume
you're using a combo box from the Controls Toolbox, and looking in the
properties list for stuff like Linked Cell and List Fill Range", rather than
just looking at format Control of the combo box (from Forms toolbar)? The
two combo boxes work differently. My example is for the combo box from the
Forms toolbar. You'll still need an intermediate cell (the linked cell of
the combo box), but we'll have to handle it differently. Which is it?
--
Earl Kiosterud
www.smokeylake.com

"Alberto Vargas" wrote in message
...
Earl,

Thanks for your response. It helped me to understant this but I still do
not
have the response I want.

When I used the INDEX formula, it appears #VALUE error. Actually I used
your
cells in the example and it did not worked either.

On regards my question, the numbers are not consecutive, so I assume that
I
will need to enter a VLOOKUP formula to have the code.

I have my list in cells A1 B2 (the list is bigger but this is just an
example)

Urban 20
Rural 99

In the "listFillRange" option I enter A1:B2, and my linked cell is A3.

Using the combo box I choose Rural and in A3 (linked cell) it shows Rural
Then in A4 I copied and paste the results from A3, and in A5 I used the
VLOOKup formula to finally determine the code for Rural so it shows "99"

So there is not way that when I choose as an example "Rural" in the combo
box, the linked cell will shows "99" directly?

Please let me kmow if there is not any option to do this just one in step
instead of four.

Thanks

Alberto





"Earl Kiosterud" wrote:

Alberto,

If your numbers in column B are consecutive (1, 2, 3... no missing
numbers),
you could use the combo box from the forms toolbar. It puts the index
number of the item you selected (2 for the second item, etc.). If
they're
not consecutive, you'll need to get the associated number in column B
from
the item selected from the first column. Here is a possible solution:

Use the combo box from the forms toolbar. Link it to an extra cell
somewhere (K15 in this example). Then in your cell where you want the
number from column B, use:

=INDEX(B1:B3,K15)

In this case, the extra cell is K15, which you could hide. It should
have 2
if the second item was selected in the combo box. The INDEX formula
should
get the second item in the column B range.
--
Earl Kiosterud
www.smokeylake.com

"Alberto Vargas" <Alberto wrote in
message
...
I am using a combo box to fill in some standard information and I know
how
they should appear in the cell, so I can copy and paste to a new cell.
But
If
my list has two cell how can I get that the destiby cells appears one
of
the
cells info only.

Example

A1 B1
Urban 1
Rural 2
I want to choose Urban from the combo box, but the information to be
stored
in the destiny cell should be 1.
I know how to do it for Urban, that is the destiny will show Urban but
I
want to get 1.


Please help

Alberto Vargas