Greetings,

I have a ComboBox on a sheet that I made from the Forms tool box.

If I choose an entry from the ComboBox drop down menu, how can a cell
formula use that data to find what is in a cell 5 columns away?

Any help will be most appreciated, thanks.

-Minitman

=OFFSET(A1,0,5)

"Minitman" wrote:

Greetings,

I have a ComboBox on a sheet that I made from the Forms tool box.

If I choose an entry from the ComboBox drop down menu, how can a cell
formula use that data to find what is in a cell 5 columns away?

Any help will be most appreciated, thanks.

-Minitman

I have a ComboBox...that I made from the Forms tool box.

A Forms combo box will return the relative number of the selected item to a
linked cell. You have to correlate that number to the source of the combo
box and then you can use that as a lookup_value.

So, you need to:

Tell us where the source for the combo box is
Link the combo box to a cell
Tell us where 5 columns away is

A combo box doesn't occupy a cell, it "floats" on top of the worksheet. Even
though you may have drawn it "in" cell A1 it does not occupy cell A1. You
can link the combo box to cell A1 and then use cell A1 in a formula to find
the item that corresponds to item number. Then, using that value, find the
item 5 columns away (wherever that is!)

--
Biff
Microsoft Excel MVP


"Minitman" wrote in message
...
Greetings,

I have a ComboBox on a sheet that I made from the Forms tool box.

If I choose an entry from the ComboBox drop down menu, how can a cell
formula use that data to find what is in a cell 5 columns away?

Any help will be most appreciated, thanks.

-Minitman

Hey Mike,

Thanks for the reply!

Hey Biff,

Good to hear from you again.

To answer your three questions:

1) "Tell us where the source for the combo box is" - Is the source the
input range in the Format Control menu? (I am very new at using these
Forms controls. I am not sure of what items are called). If it is
then the Input range is a named range called MCL_Name, which is a
dynamic named range on a sheet called CustList. Consisting of all
filled cells in column A, only 1 column wide. If the source is
something else, then I don't know.

2) "Link the combo box to a cell" - I didn't know what the "link" was
for until your explanation. So I chose A200 as the linked cell and
gave it a name "pfDisc" (pf for PrintForm sheet and Disc for the
purpose of the ComboBox which is to display the Description field of
each customer record, which is column A). And like you said it
returns the number of rows the chosen entry is from the top of the
MCL_Name list.

3) "Tell us where 5 columns away is" - 5 columns is the data that is
being requested by one of the empty cells in the form I am trying to
fill. It is the column 5 columns right of column A (column F) in the
CustName sheet (where the MCL_Name range is column A). I chose column
5 as an example.

You have given me the last pieces to this puzzle - for this, I thank
you!

With this I was able to use OFFSET to get all of the data transferred.

This is the formula looking to column 5. ( I chose to start in clStart
which is CustList!A1):

=OFFSET(clStart,pfDisc,4)

Works very well.

Again, thank you for your explanation, it really helped!!!

-Minitman

On Tue, 25 Mar 2008 01:28:57 -0400, "T. Valko"
wrote:

I have a ComboBox...that I made from the Forms tool box.

A Forms combo box will return the relative number of the selected item to a
linked cell. You have to correlate that number to the source of the combo
box and then you can use that as a lookup_value.

So, you need to:

Tell us where the source for the combo box is
Link the combo box to a cell
Tell us where 5 columns away is

A combo box doesn't occupy a cell, it "floats" on top of the worksheet. Even
though you may have drawn it "in" cell A1 it does not occupy cell A1. You
can link the combo box to cell A1 and then use cell A1 in a formula to find
the item that corresponds to item number. Then, using that value, find the
item 5 columns away (wherever that is!)

Good job of figuring out what I was talking about!

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Minitman" wrote in message
...
Hey Mike,

Thanks for the reply!

Hey Biff,

Good to hear from you again.

To answer your three questions:

1) "Tell us where the source for the combo box is" - Is the source the
input range in the Format Control menu? (I am very new at using these
Forms controls. I am not sure of what items are called). If it is
then the Input range is a named range called MCL_Name, which is a
dynamic named range on a sheet called CustList. Consisting of all
filled cells in column A, only 1 column wide. If the source is
something else, then I don't know.

2) "Link the combo box to a cell" - I didn't know what the "link" was
for until your explanation. So I chose A200 as the linked cell and
gave it a name "pfDisc" (pf for PrintForm sheet and Disc for the
purpose of the ComboBox which is to display the Description field of
each customer record, which is column A). And like you said it
returns the number of rows the chosen entry is from the top of the
MCL_Name list.

3) "Tell us where 5 columns away is" - 5 columns is the data that is
being requested by one of the empty cells in the form I am trying to
fill. It is the column 5 columns right of column A (column F) in the
CustName sheet (where the MCL_Name range is column A). I chose column
5 as an example.

You have given me the last pieces to this puzzle - for this, I thank
you!

With this I was able to use OFFSET to get all of the data transferred.

This is the formula looking to column 5. ( I chose to start in clStart
which is CustList!A1):

=OFFSET(clStart,pfDisc,4)

Works very well.

Again, thank you for your explanation, it really helped!!!

-Minitman

On Tue, 25 Mar 2008 01:28:57 -0400, "T. Valko"
wrote:

I have a ComboBox...that I made from the Forms tool box.

A Forms combo box will return the relative number of the selected item to
a
linked cell. You have to correlate that number to the source of the combo
box and then you can use that as a lookup_value.

So, you need to:

Tell us where the source for the combo box is
Link the combo box to a cell
Tell us where 5 columns away is

A combo box doesn't occupy a cell, it "floats" on top of the worksheet.
Even
though you may have drawn it "in" cell A1 it does not occupy cell A1. You
can link the combo box to cell A1 and then use cell A1 in a formula to
find
the item that corresponds to item number. Then, using that value, find the
item 5 columns away (wherever that is!)