Referencing Combobox with other cells in a different worksheet??
 

Is it possible to create a combobox in excel that shows data from a worksheet
that has two columns?

Only the data in the first column shows, but when I transmit the data it
looks to find what the full meaning of that abbrevation is....example:

Worksheet data
LG Low Grade
HG High Grade

Combobox only shows: LG and HG

If I select HG and ask it to transmit the data to new worksheet, it will not
transmit HG, but it will transmit High Grade??

Referencing Combobox with other cells in a different worksheet??
 

you can use data validation and vlookup

dynamic range w/ header row (the source for C1)
=offset($A$2,0,0,counta($A:$A)-1,1)
without header row
=offset($A$2,0,0,counta($A:$A),1)

as the source from your list

then use vlookup
=vlookup($c$1,offset($A$2,0,0,counta($A:$A)-1,2),2,false)


hope that helps

J

"Adam" wrote:

Is it possible to create a combobox in excel that shows data from a worksheet
that has two columns?

Only the data in the first column shows, but when I transmit the data it
looks to find what the full meaning of that abbrevation is....example:

Worksheet data
LG Low Grade
HG High Grade

Combobox only shows: LG and HG

If I select HG and ask it to transmit the data to new worksheet, it will not
transmit HG, but it will transmit High Grade??

Referencing Combobox with other cells in a different worksheet
 

Sorry I am new to this...what is offset?

"Gixxer_J_97" wrote:

you can use data validation and vlookup

dynamic range w/ header row (the source for C1)
=offset($A$2,0,0,counta($A:$A)-1,1)
without header row
=offset($A$2,0,0,counta($A:$A),1)

as the source from your list

then use vlookup
=vlookup($c$1,offset($A$2,0,0,counta($A:$A)-1,2),2,false)


hope that helps

J

"Adam" wrote:

Is it possible to create a combobox in excel that shows data from a worksheet
that has two columns?

Only the data in the first column shows, but when I transmit the data it
looks to find what the full meaning of that abbrevation is....example:

Worksheet data
LG Low Grade
HG High Grade

Combobox only shows: LG and HG

If I select HG and ask it to transmit the data to new worksheet, it will not
transmit HG, but it will transmit High Grade??

Referencing Combobox with other cells in a different worksheet
 

do a search for "Offset" In Excel help

basically what the offset formula is doing is selecting a range, defined as
a starting point ($A$2), adjust 0 rows down, adjust 0 columns right,
counta($A:$A) rows long, and 1 column wide)
so if you have data in A2:A10 (and nowhere else in A), the function
=offset($A$2,0,0,counta($A:$A),1)
will give you the range A2:A10

now what you do is go to Data - Validation - change Allow to List
and input that formula
obviously changing $A$2 to the start point of your first column of data, and
$A:$A to the column letters of your data

and then using the vlookup formula,
=vlookup($c$1,offset($A$2,0,0,counta($A:$A)-1,2),2,false)
change $C$1 to the location of your data validation cell
and the offset arguments to be the same as the formula for validation, but
change the 1 to a 2 instead - as you want 2 columns wide for both your
'abbreviation' and your 'full explination' columns.

hope that helps

J
"Adam" wrote:

Sorry I am new to this...what is offset?

"Gixxer_J_97" wrote:

you can use data validation and vlookup

dynamic range w/ header row (the source for C1)
=offset($A$2,0,0,counta($A:$A)-1,1)
without header row
=offset($A$2,0,0,counta($A:$A),1)

as the source from your list

then use vlookup
=vlookup($c$1,offset($A$2,0,0,counta($A:$A)-1,2),2,false)


hope that helps

J

"Adam" wrote:

Is it possible to create a combobox in excel that shows data from a worksheet
that has two columns?

Only the data in the first column shows, but when I transmit the data it
looks to find what the full meaning of that abbrevation is....example:

Worksheet data
LG Low Grade
HG High Grade

Combobox only shows: LG and HG

If I select HG and ask it to transmit the data to new worksheet, it will not
transmit HG, but it will transmit High Grade??

Referencing Combobox with other cells in a different worksheet??
 

You can also use a control toolbox Combo, set the ColumnCount property to 2,
and the ListFillRange to your data.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Adam" wrote in message
...
Is it possible to create a combobox in excel that shows data from a
worksheet
that has two columns?

Only the data in the first column shows, but when I transmit the data it
looks to find what the full meaning of that abbrevation is....example:

Worksheet data
LG Low Grade
HG High Grade

Combobox only shows: LG and HG

If I select HG and ask it to transmit the data to new worksheet, it will
not
transmit HG, but it will transmit High Grade??