Hide ALL dropdowns on autofilter  
Author Message
erudum





PostPosted: Sun Sep 30 14:22:51 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter

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

Excel162  
 
 
Dave





PostPosted: Sun Sep 30 14:22:51 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter Debra Dalgleish shows how to hide the dropdown arrows for Autofilter here:
http://contextures.com/xlautofilter03.html#Hide


>
> 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
 
 
Kenny





PostPosted: Sun Sep 30 14:57:02 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter 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!




> did you include the sheet number as below
>
> =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
>

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

> >
> > > 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.
> > >

> > >
> > > > 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
> > > >




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

> >
> > 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





PostPosted: Sun Sep 30 15:16:10 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter Did you mean to reply in your thread about hiding the dropdown arrows in the
autofilter range?



>
> 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!
>

>
> > did you include the sheet number as below
> >
> > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> >

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

> > >
> > > > 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.
> > > >

> > > >
> > > > > 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
> > > > >
>

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

> > >
> > > 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
 
 
Kenny





PostPosted: Sun Sep 30 15:28:01 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter yes i was wondering if u could help with this. No one can help me it seems




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

> >
> > 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!
> >

> >
> > > did you include the sheet number as below
> > >
> > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> > >

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

> > > >
> > > > > 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.
> > > > >

> > > > >
> > > > > > 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
> > > > > >
> >

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

> > > >
> > > > 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





PostPosted: Sun Sep 30 16:33:45 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter 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.




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

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

> > >
> > > 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!
> > >

> > >
> > > > did you include the sheet number as below
> > > >
> > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> > > >

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

> > > > >
> > > > > > 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.
> > > > > >

> > > > > >
> > > > > > > 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
> > > > > > >
> > >

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

> > > > >
> > > > > 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





PostPosted: Sun Sep 30 16:37:05 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter And I see that you posted another time in a different newsgroup.

I'll drop out of this discussion.

Good luck.


>
> 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.
>

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

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

> > > >
> > > > 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!
> > > >

> > > >
> > > > > did you include the sheet number as below
> > > > >
> > > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> > > > >

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

> > > > > >
> > > > > > > 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.
> > > > > > >

> > > > > > >
> > > > > > > > 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
> > > > > > > >
> > > >

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

> > > > > >
> > > > > > 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
 
 
Kenny





PostPosted: Sun Sep 30 17:01:02 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter 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




> And I see that you posted another time in a different newsgroup.
>
> I'll drop out of this discussion.
>
> Good luck.
>

> >
> > 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.
> >

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

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

> > > > >
> > > > > 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!
> > > > >

> > > > >
> > > > > > did you include the sheet number as below
> > > > > >
> > > > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> > > > > >

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

> > > > > > >
> > > > > > > > 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.
> > > > > > > >

> > > > > > > >
> > > > > > > > > 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
> > > > > > > > >
> > > > >

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

> > > > > > >
> > > > > > > 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





PostPosted: Sun Sep 30 18:04:22 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter Did you try the =vlookup() formula?


>
> 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
>

>
> > And I see that you posted another time in a different newsgroup.
> >
> > I'll drop out of this discussion.
> >
> > Good luck.
> >

> > >
> > > 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.
> > >

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

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

> > > > > >
> > > > > > 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!
> > > > > >

> > > > > >
> > > > > > > did you include the sheet number as below
> > > > > > >
> > > > > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> > > > > > >

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

> > > > > > > >
> > > > > > > > > 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.
> > > > > > > > >

> > > > > > > > >
> > > > > > > > > > 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
> > > > > > > > > >
> > > > > >

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

> > > > > > > >
> > > > > > > > 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
 
 
Kenny





PostPosted: Sun Sep 30 20:08:01 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter yes i did, but I keep getting errors in the list validation setup window,
when I try to use that in the formula




> Did you try the =vlookup() formula?
>

> >
> > 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
> >

> >
> > > And I see that you posted another time in a different newsgroup.
> > >
> > > I'll drop out of this discussion.
> > >
> > > Good luck.
> > >

> > > >
> > > > 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.
> > > >

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

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

> > > > > > >
> > > > > > > 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!
> > > > > > >

> > > > > > >
> > > > > > > > did you include the sheet number as below
> > > > > > > >
> > > > > > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> > > > > > > >

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

> > > > > > > > >
> > > > > > > > > > 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.
> > > > > > > > > >

> > > > > > > > > >
> > > > > > > > > > > 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
> > > > > > > > > > >
> > > > > > >

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

> > > > > > > > >
> > > > > > > > > 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
>
 
 
Peo





PostPosted: Sun Sep 30 20:52:07 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter You can't have a validation list and a formula in the same cell, Dave meant
in another cell


--

Regards,

Peo Sjoblom






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

>
>> Did you try the =vlookup() formula?
>>

>> >
>> > 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
>> >

>> >
>> > > And I see that you posted another time in a different newsgroup.
>> > >
>> > > I'll drop out of this discussion.
>> > >
>> > > Good luck.
>> > >

>> > > >
>> > > > 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.
>> > > >

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

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

>> > > > > > >
>> > > > > > > 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!
>> > > > > > >

>> > > > > > >
>> > > > > > > > did you include the sheet number as below
>> > > > > > > >
>> > > > > > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
>> > > > > > > >

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

>> > > > > > > > >
>> > > > > > > > > > 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.
>> > > > > > > > > >

>> > > > > > > > > >
>> > > > > > > > > > > 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
>> > > > > > > > > > >
>> > > > > > >

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

>> > > > > > > > >
>> > > > > > > > > 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
>>


 
 
Kenny





PostPosted: Sun Sep 30 21:17:00 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter 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?




> You can't have a validation list and a formula in the same cell, Dave meant
> in another cell
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
>
>


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

> >
> >> Did you try the =vlookup() formula?
> >>

> >> >
> >> > 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
> >> >

> >> >
> >> > > And I see that you posted another time in a different newsgroup.
> >> > >
> >> > > I'll drop out of this discussion.
> >> > >
> >> > > Good luck.
> >> > >

> >> > > >
> >> > > > 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.
> >> > > >

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

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

> >> > > > > > >
> >> > > > > > > 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!
> >> > > > > > >

> >> > > > > > >
> >> > > > > > > > did you include the sheet number as below
> >> > > > > > > >
> >> > > > > > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> >> > > > > > > >

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

> >> > > > > > > > >
> >> > > > > > > > > > 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.
> >> > > > > > > > > >

> >> > > > > > > > > >
> >> > > > > > > > > > > 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
> >> > > > > > > > > > >
> >> > > > > > >

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

> >> > > > > > > > >
> >> > > > > > > > > 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
> >>
>
>
>
 
 
Roger





PostPosted: Mon Oct 01 00:35:53 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter 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





> 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?
>
>

>
>> You can't have a validation list and a formula in the same cell, Dave
>> meant
>> in another cell
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>>
>>


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

>> >
>> >> Did you try the =vlookup() formula?
>> >>

>> >> >
>> >> > 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
>> >> >

>> >> >
>> >> > > And I see that you posted another time in a different newsgroup.
>> >> > >
>> >> > > I'll drop out of this discussion.
>> >> > >
>> >> > > Good luck.
>> >> > >

>> >> > > >
>> >> > > > 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.
>> >> > > >

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

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

>> >> > > > > > >
>> >> > > > > > > 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!
>> >> > > > > > >

>> >> > > > > > >
>> >> > > > > > > > did you include the sheet number as below
>> >> > > > > > > >
>> >> > > > > > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
>> >> > > > > > > >

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

>> >> > > > > > > > >
>> >> > > > > > > > > > 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.
>> >> > > > > > > > > >

>> >> > > > > > > > > >
>> >> > > > > > > > > > > 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
>> >> > > > > > > > > > >
>> >> > > > > > >

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

>> >> > > > > > > > >
>> >> > > > > > > > > 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





PostPosted: Mon Oct 01 04:58:39 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter 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.



>
> 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?
>

>
> > You can't have a validation list and a formula in the same cell, Dave meant
> > in another cell
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> >
> >


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

> > >
> > >> Did you try the =vlookup() formula?
> > >>

> > >> >
> > >> > 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
> > >> >

> > >> >
> > >> > > And I see that you posted another time in a different newsgroup.
> > >> > >
> > >> > > I'll drop out of this discussion.
> > >> > >
> > >> > > Good luck.
> > >> > >

> > >> > > >
> > >> > > > 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.
> > >> > > >

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

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

> > >> > > > > > >
> > >> > > > > > > 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!
> > >> > > > > > >

> > >> > > > > > >
> > >> > > > > > > > did you include the sheet number as below
> > >> > > > > > > >
> > >> > > > > > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> > >> > > > > > > >

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

> > >> > > > > > > > >
> > >> > > > > > > > > > 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.
> > >> > > > > > > > > >

> > >> > > > > > > > > >
> > >> > > > > > > > > > > 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
> > >> > > > > > > > > > >
> > >> > > > > > >

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

> > >> > > > > > > > >
> > >> > > > > > > > > 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
 
 
Dave





PostPosted: Mon Oct 01 04:59:28 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter 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)).


>
> 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?
>

>
> > You can't have a validation list and a formula in the same cell, Dave meant
> > in another cell
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> >
> >


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

> > >
> > >> Did you try the =vlookup() formula?
> > >>

> > >> >
> > >> > 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
> > >> >

> > >> >
> > >> > > And I see that you posted another time in a different newsgroup.
> > >> > >
> > >> > > I'll drop out of this discussion.
> > >> > >
> > >> > > Good luck.
> > >> > >

> > >> > > >
> > >> > > > 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.
> > >> > > >

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

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

> > >> > > > > > >
> > >> > > > > > > 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!
> > >> > > > > > >

> > >> > > > > > >
> > >> > > > > > > > did you include the sheet number as below
> > >> > > > > > > >
> > >> > > > > > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> > >> > > > > > > >

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

> > >> > > > > > > > >
> > >> > > > > > > > > > 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.
> > >> > > > > > > > > >

> > >> > > > > > > > > >
> > >> > > > > > > > > > > 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
> > >> > > > > > > > > > >
> > >> > > > > > >

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

> > >> > > > > > > > >
> > >> > > > > > > > > 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
 
 
Kenny





PostPosted: Mon Oct 01 15:33:01 PDT 2007 Top

Excel Misc >> Hide ALL dropdowns on autofilter Thanks so much for the help!



> 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)).
>

> >
> > 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?
> >

> >
> > > You can't have a validation list and a formula in the same cell, Dave meant
> > > in another cell
> > >
> > >
> > > --
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > >
> > >
> > >


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

> > > >
> > > >> Did you try the =vlookup() formula?
> > > >>

> > > >> >
> > > >> > 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
> > > >> >

> > > >> >
> > > >> > > And I see that you posted another time in a different newsgroup.
> > > >> > >
> > > >> > > I'll drop out of this discussion.
> > > >> > >
> > > >> > > Good luck.
> > > >> > >

> > > >> > > >
> > > >> > > > 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.
> > > >> > > >

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

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

> > > >> > > > > > >
> > > >> > > > > > > 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!
> > > >> > > > > > >

> > > >> > > > > > >
> > > >> > > > > > > > did you include the sheet number as below
> > > >> > > > > > > >
> > > >> > > > > > > > =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100)
> > > >> > > > > > > >

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

> > > >> > > > > > > > >
> > > >> > > > > > > > > > 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.
> > > >> > > > > > > > > >

> > > >> > > > > > > > > >
> > > >> > > > > > > > > > > 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
> > > >> > > > > > > > > > >
> > > >> > > > > > >

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

> > > >> > > > > > > > >
> > > >> > > > > > > > > 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
>