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