After the code below. I want to hide all the dropdown auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

Debra Dalgleish shows how to hide the dropdown arrows for Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it
show a list inside the drop down that matches column a on sheet 2, when I
click on a choice it will actually populate the cell with the corresponind
choice in column b from sheet 2. You can not use sheet references in the list
validation formula. I have the data range in colum a labled as
CodeDescription I have column B labled as Code and both columns together
labled as CodeTable.... Is this possible? Also I would like the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!


"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array. So if you know the row
number of the first member (row(A10)) then you simply add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on
the list validation choice, I want it to return the value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still cannot figure this out,
please tell me what i need to put in the code list validation bax, thanks so
much



"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson

Did you mean to reply in your thread about hiding the dropdown arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it
show a list inside the drop down that matches column a on sheet 2, when I
click on a choice it will actually populate the cell with the corresponind
choice in column b from sheet 2. You can not use sheet references in the list
validation formula. I have the data range in colum a labled as
CodeDescription I have column B labled as Code and both columns together
labled as CodeTable.... Is this possible? Also I would like the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array. So if you know the row
number of the first member (row(A10)) then you simply add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on
the list validation choice, I want it to return the value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still cannot figure this out,
please tell me what i need to put in the code list validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson

yes i was wondering if u could help with this. No one can help me it seems


"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it
show a list inside the drop down that matches column a on sheet 2, when I
click on a choice it will actually populate the cell with the corresponind
choice in column b from sheet 2. You can not use sheet references in the list
validation formula. I have the data range in colum a labled as
CodeDescription I have column B labled as Code and both columns together
labled as CodeTable.... Is this possible? Also I would like the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array. So if you know the row
number of the first member (row(A10)) then you simply add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on
the list validation choice, I want it to return the value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still cannot figure this out,
please tell me what i need to put in the code list validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson

You can use a range/list on another worksheet if you name that range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.



Kenny wrote:

yes i was wondering if u could help with this. No one can help me it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it
show a list inside the drop down that matches column a on sheet 2, when I
click on a choice it will actually populate the cell with the corresponind
choice in column b from sheet 2. You can not use sheet references in the list
validation formula. I have the data range in colum a labled as
CodeDescription I have column B labled as Code and both columns together
labled as CodeTable.... Is this possible? Also I would like the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array. So if you know the row
number of the first member (row(A10)) then you simply add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on
the list validation choice, I want it to return the value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still cannot figure this out,
please tell me what i need to put in the code list validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.

Dave Peterson wrote:

You can use a range/list on another worksheet if you name that range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.

Kenny wrote:

yes i was wondering if u could help with this. No one can help me it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it
show a list inside the drop down that matches column a on sheet 2, when I
click on a choice it will actually populate the cell with the corresponind
choice in column b from sheet 2. You can not use sheet references in the list
validation formula. I have the data range in colum a labled as
CodeDescription I have column B labled as Code and both columns together
labled as CodeTable.... Is this possible? Also I would like the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array. So if you know the row
number of the first member (row(A10)) then you simply add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on
the list validation choice, I want it to return the value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still cannot figure this out,
please tell me what i need to put in the code list validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Dave I really appreciate your help. Yes I did post to another, but it is not
helping me. They always refer me to this site, but the answer is not
contained in this site. I know how to create a list validation fromt sheet 2,
here is the issue. Column A in sheet 2 has the list of items i want to select
from, column b has the info I want to pop into the cell i use the list
validation on. IE:

Sheet2

Column A Column B
Cabbage Green
Squash Yellow
Egg Plant Black

The list validation will show the list off column a, but when I select that
vegie it will pop the color into the cell. I only want one list box.... Thanks


"Dave Peterson" wrote:

And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.

Dave Peterson wrote:

You can use a range/list on another worksheet if you name that range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.

Kenny wrote:

yes i was wondering if u could help with this. No one can help me it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it
show a list inside the drop down that matches column a on sheet 2, when I
click on a choice it will actually populate the cell with the corresponind
choice in column b from sheet 2. You can not use sheet references in the list
validation formula. I have the data range in colum a labled as
CodeDescription I have column B labled as Code and both columns together
labled as CodeTable.... Is this possible? Also I would like the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array. So if you know the row
number of the first member (row(A10)) then you simply add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on
the list validation choice, I want it to return the value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still cannot figure this out,
please tell me what i need to put in the code list validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Did you try the =vlookup() formula?

Kenny wrote:

Dave I really appreciate your help. Yes I did post to another, but it is not
helping me. They always refer me to this site, but the answer is not
contained in this site. I know how to create a list validation fromt sheet 2,
here is the issue. Column A in sheet 2 has the list of items i want to select
from, column b has the info I want to pop into the cell i use the list
validation on. IE:

Sheet2

Column A Column B
Cabbage Green
Squash Yellow
Egg Plant Black

The list validation will show the list off column a, but when I select that
vegie it will pop the color into the cell. I only want one list box.... Thanks

"Dave Peterson" wrote:

And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.

Dave Peterson wrote:

You can use a range/list on another worksheet if you name that range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.

Kenny wrote:

yes i was wondering if u could help with this. No one can help me it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it
show a list inside the drop down that matches column a on sheet 2, when I
click on a choice it will actually populate the cell with the corresponind
choice in column b from sheet 2. You can not use sheet references in the list
validation formula. I have the data range in colum a labled as
CodeDescription I have column B labled as Code and both columns together
labled as CodeTable.... Is this possible? Also I would like the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array. So if you know the row
number of the first member (row(A10)) then you simply add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on
the list validation choice, I want it to return the value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still cannot figure this out,
please tell me what i need to put in the code list validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

yes i did, but I keep getting errors in the list validation setup window,
when I try to use that in the formula


"Dave Peterson" wrote:

Did you try the =vlookup() formula?

Kenny wrote:

Dave I really appreciate your help. Yes I did post to another, but it is not
helping me. They always refer me to this site, but the answer is not
contained in this site. I know how to create a list validation fromt sheet 2,
here is the issue. Column A in sheet 2 has the list of items i want to select
from, column b has the info I want to pop into the cell i use the list
validation on. IE:

Sheet2

Column A Column B
Cabbage Green
Squash Yellow
Egg Plant Black

The list validation will show the list off column a, but when I select that
vegie it will pop the color into the cell. I only want one list box.... Thanks

"Dave Peterson" wrote:

And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.

Dave Peterson wrote:

You can use a range/list on another worksheet if you name that range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.

Kenny wrote:

yes i was wondering if u could help with this. No one can help me it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it
show a list inside the drop down that matches column a on sheet 2, when I
click on a choice it will actually populate the cell with the corresponind
choice in column b from sheet 2. You can not use sheet references in the list
validation formula. I have the data range in colum a labled as
CodeDescription I have column B labled as Code and both columns together
labled as CodeTable.... Is this possible? Also I would like the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array. So if you know the row
number of the first member (row(A10)) then you simply add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on
the list validation choice, I want it to return the value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still cannot figure this out,
please tell me what i need to put in the code list validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3, Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12, Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

You can't have a validation list and a formula in the same cell, Dave meant
in another cell


--

Regards,

Peo Sjoblom




"Kenny" wrote in message
...
yes i did, but I keep getting errors in the list validation setup window,
when I try to use that in the formula


"Dave Peterson" wrote:

Did you try the =vlookup() formula?

Kenny wrote:

Dave I really appreciate your help. Yes I did post to another, but it
is not
helping me. They always refer me to this site, but the answer is not
contained in this site. I know how to create a list validation fromt
sheet 2,
here is the issue. Column A in sheet 2 has the list of items i want to
select
from, column b has the info I want to pop into the cell i use the list
validation on. IE:

Sheet2

Column A Column B
Cabbage Green
Squash Yellow
Egg Plant Black

The list validation will show the list off column a, but when I select
that
vegie it will pop the color into the cell. I only want one list box....
Thanks

"Dave Peterson" wrote:

And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.

Dave Peterson wrote:

You can use a range/list on another worksheet if you name that
range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you
could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.

Kenny wrote:

yes i was wondering if u could help with this. No one can help me
it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown
arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using
a list
validation on the cell in sheet 1. I want to click on the
drop down and it
show a list inside the drop down that matches column a on
sheet 2, when I
click on a choice it will actually populate the cell with the
corresponind
choice in column b from sheet 2. You can not use sheet
references in the list
validation formula. I have the data range in colum a labled
as
CodeDescription I have column B labled as Code and both
columns together
labled as CodeTable.... Is this possible? Also I would like
the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce
sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1
then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array.
So if you know the row
number of the first member (row(A10)) then you simply
add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION,
when the drop down is
selected it will show sheet2 column A choices, no
biggie so far, but based on
the list validation choice, I want it to return the
value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a
choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still
cannot figure this out,
please tell me what i need to put in the code list
validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for
Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown
auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3,
Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12,
Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

Okay can you tell me another way to do what I need to do?

I want a list box, I want to choose from column a from another sheet, I want
to populate the same cell with the corresponding row from column b. Is this
possible?


"Peo Sjoblom" wrote:

You can't have a validation list and a formula in the same cell, Dave meant
in another cell


--

Regards,

Peo Sjoblom




"Kenny" wrote in message
...
yes i did, but I keep getting errors in the list validation setup window,
when I try to use that in the formula


"Dave Peterson" wrote:

Did you try the =vlookup() formula?

Kenny wrote:

Dave I really appreciate your help. Yes I did post to another, but it
is not
helping me. They always refer me to this site, but the answer is not
contained in this site. I know how to create a list validation fromt
sheet 2,
here is the issue. Column A in sheet 2 has the list of items i want to
select
from, column b has the info I want to pop into the cell i use the list
validation on. IE:

Sheet2

Column A Column B
Cabbage Green
Squash Yellow
Egg Plant Black

The list validation will show the list off column a, but when I select
that
vegie it will pop the color into the cell. I only want one list box....
Thanks

"Dave Peterson" wrote:

And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.

Dave Peterson wrote:

You can use a range/list on another worksheet if you name that
range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you
could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.

Kenny wrote:

yes i was wondering if u could help with this. No one can help me
it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown
arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using
a list
validation on the cell in sheet 1. I want to click on the
drop down and it
show a list inside the drop down that matches column a on
sheet 2, when I
click on a choice it will actually populate the cell with the
corresponind
choice in column b from sheet 2. You can not use sheet
references in the list
validation formula. I have the data range in colum a labled
as
CodeDescription I have column B labled as Code and both
columns together
labled as CodeTable.... Is this possible? Also I would like
the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce
sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1
then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array.
So if you know the row
number of the first member (row(A10)) then you simply
add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION,
when the drop down is
selected it will show sheet2 column A choices, no
biggie so far, but based on
the list validation choice, I want it to return the
value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a
choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still
cannot figure this out,
please tell me what i need to put in the code list
validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for
Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown
auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3,
Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12,
Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

Hi Kenny

You will find the answer on Debra Dalgleish's site. Take a look at dependent
dropdown lists
http://www.contextures.com/xlDataVal13.html#Depend
--
Regards
Roger Govier



"Kenny" wrote in message
...
Okay can you tell me another way to do what I need to do?

I want a list box, I want to choose from column a from another sheet, I
want
to populate the same cell with the corresponding row from column b. Is
this
possible?


"Peo Sjoblom" wrote:

You can't have a validation list and a formula in the same cell, Dave
meant
in another cell


--

Regards,

Peo Sjoblom




"Kenny" wrote in message
...
yes i did, but I keep getting errors in the list validation setup
window,
when I try to use that in the formula


"Dave Peterson" wrote:

Did you try the =vlookup() formula?

Kenny wrote:

Dave I really appreciate your help. Yes I did post to another, but
it
is not
helping me. They always refer me to this site, but the answer is not
contained in this site. I know how to create a list validation fromt
sheet 2,
here is the issue. Column A in sheet 2 has the list of items i want
to
select
from, column b has the info I want to pop into the cell i use the
list
validation on. IE:

Sheet2

Column A Column B
Cabbage Green
Squash Yellow
Egg Plant Black

The list validation will show the list off column a, but when I
select
that
vegie it will pop the color into the cell. I only want one list
box....
Thanks

"Dave Peterson" wrote:

And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.

Dave Peterson wrote:

You can use a range/list on another worksheet if you name that
range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you
could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.

Kenny wrote:

yes i was wondering if u could help with this. No one can help
me
it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the
dropdown
arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am
using
a list
validation on the cell in sheet 1. I want to click on the
drop down and it
show a list inside the drop down that matches column a on
sheet 2, when I
click on a choice it will actually populate the cell with
the
corresponind
choice in column b from sheet 2. You can not use sheet
references in the list
validation formula. I have the data range in colum a
labled
as
CodeDescription I have column B labled as Code and both
columns together
labled as CodeTable.... Is this possible? Also I would
like
the formula to
suppress blank spots that may be contained in a or b
offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to
refernce
sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1
then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array.
So if you know the row
number of the first member (row(A10)) then you
simply
add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION,
when the drop down is
selected it will show sheet2 column A choices, no
biggie so far, but based on
the list validation choice, I want it to return
the
value in the
corresponding row in sheet2 column b in cell I3
IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a
choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still
cannot figure this out,
please tell me what i need to put in the code list
validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows
for
Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown
auto filter arrows on
each column, but I want the filter I applied to
stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3,
Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter
Field:=12,
Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

If you're using xl97, then this won't work.

I created a named range called myList that was used for the data|validation
rules.

I rightclicked on the worksheet tab that held the cell with data|validation. I
selected View Code and pasted this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim res As Variant

'on cell at a time
If Target.Cells.Count 1 Then Exit Sub

'only look at A1 -- where the data|validation cell is
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

res = Application.VLookup(Target.Value, _
Worksheets("sheet2").Range("mylist").Resize(, 2), 2, False)

Application.EnableEvents = False
If IsError(res) Then
'this shouldn't happen
Target.Value = "Missing"
Else
Target.Value = res
End If

errHandler:
Application.EnableEvents = True

End Sub

=======
Change A1 to the cell's address that has that data|validation.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chip Pearson has some instructions on events:
http://www.cpearson.com/excel/Events.aspx

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm

And the macro will not work if the user disables macros or disables events.


Kenny wrote:

Okay can you tell me another way to do what I need to do?

I want a list box, I want to choose from column a from another sheet, I want
to populate the same cell with the corresponding row from column b. Is this
possible?

"Peo Sjoblom" wrote:

You can't have a validation list and a formula in the same cell, Dave meant
in another cell


--

Regards,

Peo Sjoblom




"Kenny" wrote in message
...
yes i did, but I keep getting errors in the list validation setup window,
when I try to use that in the formula


"Dave Peterson" wrote:

Did you try the =vlookup() formula?

Kenny wrote:

Dave I really appreciate your help. Yes I did post to another, but it
is not
helping me. They always refer me to this site, but the answer is not
contained in this site. I know how to create a list validation fromt
sheet 2,
here is the issue. Column A in sheet 2 has the list of items i want to
select
from, column b has the info I want to pop into the cell i use the list
validation on. IE:

Sheet2

Column A Column B
Cabbage Green
Squash Yellow
Egg Plant Black

The list validation will show the list off column a, but when I select
that
vegie it will pop the color into the cell. I only want one list box....
Thanks

"Dave Peterson" wrote:

And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.

Dave Peterson wrote:

You can use a range/list on another worksheet if you name that
range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you
could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.

Kenny wrote:

yes i was wondering if u could help with this. No one can help me
it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown
arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using
a list
validation on the cell in sheet 1. I want to click on the
drop down and it
show a list inside the drop down that matches column a on
sheet 2, when I
click on a choice it will actually populate the cell with the
corresponind
choice in column b from sheet 2. You can not use sheet
references in the list
validation formula. I have the data range in colum a labled
as
CodeDescription I have column B labled as Code and both
columns together
labled as CodeTable.... Is this possible? Also I would like
the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce
sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1
then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array.
So if you know the row
number of the first member (row(A10)) then you simply
add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION,
when the drop down is
selected it will show sheet2 column A choices, no
biggie so far, but based on
the list validation choice, I want it to return the
value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a
choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still
cannot figure this out,
please tell me what i need to put in the code list
validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for
Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown
auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3,
Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12,
Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson





--

Dave Peterson

Ps. I'd really use a different cell. I think it makes things easier and less
prone to failure (when macros or events are disabled (for instance)).

Kenny wrote:

Okay can you tell me another way to do what I need to do?

I want a list box, I want to choose from column a from another sheet, I want
to populate the same cell with the corresponding row from column b. Is this
possible?

"Peo Sjoblom" wrote:

You can't have a validation list and a formula in the same cell, Dave meant
in another cell


--

Regards,

Peo Sjoblom




"Kenny" wrote in message
...
yes i did, but I keep getting errors in the list validation setup window,
when I try to use that in the formula


"Dave Peterson" wrote:

Did you try the =vlookup() formula?

Kenny wrote:

Dave I really appreciate your help. Yes I did post to another, but it
is not
helping me. They always refer me to this site, but the answer is not
contained in this site. I know how to create a list validation fromt
sheet 2,
here is the issue. Column A in sheet 2 has the list of items i want to
select
from, column b has the info I want to pop into the cell i use the list
validation on. IE:

Sheet2

Column A Column B
Cabbage Green
Squash Yellow
Egg Plant Black

The list validation will show the list off column a, but when I select
that
vegie it will pop the color into the cell. I only want one list box....
Thanks

"Dave Peterson" wrote:

And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.

Dave Peterson wrote:

You can use a range/list on another worksheet if you name that
range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you
could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.

Kenny wrote:

yes i was wondering if u could help with this. No one can help me
it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown
arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using
a list
validation on the cell in sheet 1. I want to click on the
drop down and it
show a list inside the drop down that matches column a on
sheet 2, when I
click on a choice it will actually populate the cell with the
corresponind
choice in column b from sheet 2. You can not use sheet
references in the list
validation formula. I have the data range in colum a labled
as
CodeDescription I have column B labled as Code and both
columns together
labled as CodeTable.... Is this possible? Also I would like
the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce
sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1
then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array.
So if you know the row
number of the first member (row(A10)) then you simply
add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION,
when the drop down is
selected it will show sheet2 column A choices, no
biggie so far, but based on
the list validation choice, I want it to return the
value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a
choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still
cannot figure this out,
please tell me what i need to put in the code list
validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for
Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown
auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3,
Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12,
Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson





--

Dave Peterson

Thanks so much for the help!

"Dave Peterson" wrote:

Ps. I'd really use a different cell. I think it makes things easier and less
prone to failure (when macros or events are disabled (for instance)).

Kenny wrote:

Okay can you tell me another way to do what I need to do?

I want a list box, I want to choose from column a from another sheet, I want
to populate the same cell with the corresponding row from column b. Is this
possible?

"Peo Sjoblom" wrote:

You can't have a validation list and a formula in the same cell, Dave meant
in another cell


--

Regards,

Peo Sjoblom




"Kenny" wrote in message
...
yes i did, but I keep getting errors in the list validation setup window,
when I try to use that in the formula


"Dave Peterson" wrote:

Did you try the =vlookup() formula?

Kenny wrote:

Dave I really appreciate your help. Yes I did post to another, but it
is not
helping me. They always refer me to this site, but the answer is not
contained in this site. I know how to create a list validation fromt
sheet 2,
here is the issue. Column A in sheet 2 has the list of items i want to
select
from, column b has the info I want to pop into the cell i use the list
validation on. IE:

Sheet2

Column A Column B
Cabbage Green
Squash Yellow
Egg Plant Black

The list validation will show the list off column a, but when I select
that
vegie it will pop the color into the cell. I only want one list box....
Thanks

"Dave Peterson" wrote:

And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.

Dave Peterson wrote:

You can use a range/list on another worksheet if you name that
range.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

I don't understand the second part of your question.

If you meant you wanted to retrieve a value from column B, you
could use
something like:
=if(a2="","",vlookup(a2,sheet2!a:b,2,false))
Where A2 held the dropdown of the available choices.

Kenny wrote:

yes i was wondering if u could help with this. No one can help me
it seems

"Dave Peterson" wrote:

Did you mean to reply in your thread about hiding the dropdown
arrows in the
autofilter range?


Kenny wrote:

Thanks so much can you help with this post.

maybe i am not explaining this well enough sorry. I am using
a list
validation on the cell in sheet 1. I want to click on the
drop down and it
show a list inside the drop down that matches column a on
sheet 2, when I
click on a choice it will actually populate the cell with the
corresponind
choice in column b from sheet 2. You can not use sheet
references in the list
validation formula. I have the data range in colum a labled
as
CodeDescription I have column B labled as Code and both
columns together
labled as CodeTable.... Is this possible? Also I would like
the formula to
suppress blank spots that may be contained in a or b offset?

Thanks!

"Joel" wrote:

did you include the sheet number as below

=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)

"Kenny" wrote:

this does not work, the list validation needs to refernce
sheet2 not sheet 1,
i get an error with your code


"Joel" wrote:

You have to use match. If the table starts in row 1
then
=MATCH($A$1,$A$2:$A$100)

if the table starts in some other row then
=MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1

Match will give you the index number into the array.
So if you know the row
number of the first member (row(A10)) then you simply
add the index return by
match to the starting row number.

"Kenny" wrote:

sheet1 cell I3, I want to creat a LIST VALIDATION,
when the drop down is
selected it will show sheet2 column A choices, no
biggie so far, but based on
the list validation choice, I want it to return the
value in the
corresponding row in sheet2 column b in cell I3 IE:

Sheet 2
Column A Column B
Hard Drive 2
Monitor 3
keyboard 4

sheet 1 cell I3 list validation shows column a
choices, I select hard drive
it will put a 2 in cell I3 not Hard Drive????

Also I have looked at contextures code and still
cannot figure this out,
please tell me what i need to put in the code list
validation bax, thanks so
much


"Dave Peterson" wrote:

Debra Dalgleish shows how to hide the dropdown arrows for
Autofilter he
http://contextures.com/xlautofilter03.html#Hide

Kenny wrote:

After the code below. I want to hide all the dropdown
auto filter arrows on
each column, but I want the filter I applied to stay???

Private Sub Band3_Click()
AutoFilterMode = False
Worksheets("Tracker").Range("A2").AutoFilter Field:=3,
Criteria1:="Band 3"
Worksheets("Tracker").Range("A2").AutoFilter Field:=12,
Criteria1:="="
'AutoFilterMode = False
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson





--

Dave Peterson