Data Validation - Dynamic

Hello. I have a simple table with 2 columns. In col A, the words "Open" or
"Closed". In column B, a vendor. On a different sheet, I would like to
have data validation drop-down with ONLY the vendors that have "Open" next
to them. Possible? Thanks so much!

See http://www.xldynamic.com/source/xld.Dropdowns.html

--

HTH

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


"Steph" <[email protected]> wrote in message
news:[email protected]...
> Hello. I have a simple table with 2 columns. In col A, the words "Open"
or
> "Closed". In column B, a vendor. On a different sheet, I would like to
> have data validation drop-down with ONLY the vendors that have "Open" next
> to them. Possible? Thanks so much!
>
>
>

You don't need code for this.

You can sort the original columns by the "closed/open" column ("open" on top); replicate the columns' data in the other sheet with a simple reference; and then have an OFFSET formula as your validation range.

assuming you bring over the original data into A101:B111, the validation "source" would be

=OFFSET($B$101,0,0,MATCH("Closed",$A$101:$A$111,0)-1,1)

If you don't want to sort the original data, then use an array formula in the other sheet as your validation range.

Assuming again original data in A1:B11, select any out-of-the way 11 row x 1 col range, enter in formula bar

=INDEX(Sheet1!$B:$B,SMALL(IF(Sheet1!$A$1:$A$11="open",ROW(Sheet1!$A$1:$A$11),99999),ROW()))

hit Ctrl+Shift+Return

Point validation to that range

You can hide the zeroes in the drop-down through the offset formula above.

Wow, that's awfully complicated! I thought this would be a simple formula
in the data validation section!!

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> See http://www.xldynamic.com/source/xld.Dropdowns.html
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Steph" <[email protected]> wrote in message
> news:[email protected]...
> > Hello. I have a simple table with 2 columns. In col A, the words
"Open"
> or
> > "Closed". In column B, a vendor. On a different sheet, I would like to
> > have data validation drop-down with ONLY the vendors that have "Open"
next
> > to them. Possible? Thanks so much!
> >
> >
> >
>
>

I assumed you wanted to switch. If you only want to show the Open items,
give the open items a workbook name, and then add a DV with a list linked to
that name.

--

HTH

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


"Steph" <[email protected]> wrote in message
news:[email protected]...
> Wow, that's awfully complicated! I thought this would be a simple formula
> in the data validation section!!
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > See http://www.xldynamic.com/source/xld.Dropdowns.html
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Steph" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello. I have a simple table with 2 columns. In col A, the words
> "Open"
> > or
> > > "Closed". In column B, a vendor. On a different sheet, I would like
to
> > > have data validation drop-down with ONLY the vendors that have "Open"
> next
> > > to them. Possible? Thanks so much!
> > >
> > >
> > >
> >
> >
>
>

Thanks Bob. I guess that was the root of my question - How would I give
only the Open items a Name?

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> I assumed you wanted to switch. If you only want to show the Open items,
> give the open items a workbook name, and then add a DV with a list linked
to
> that name.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Steph" <[email protected]> wrote in message
> news:[email protected]...
> > Wow, that's awfully complicated! I thought this would be a simple
formula
> > in the data validation section!!
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:[email protected]...
> > > See http://www.xldynamic.com/source/xld.Dropdowns.html
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Steph" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hello. I have a simple table with 2 columns. In col A, the words
> > "Open"
> > > or
> > > > "Closed". In column B, a vendor. On a different sheet, I would
like
> to
> > > > have data validation drop-down with ONLY the vendors that have
"Open"
> > next
> > > > to them. Possible? Thanks so much!
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

post the exact ranges involved and i'll post some formulas you can just paste

the OFFSET function's reason for being is to create dynamic ranges

Select the Open items on the worksheet, all of them, then goto menu
Insert>Name>Define... and then just type a meaningful name in the Names In
Workbook field. The Refersto field will be completed.

Then on the other sheet, select the field that you want the DV on and
select Data>Validation and in the Allow box select List, and then in the
Source box type =myRange where myRange is the name you just gave the Open
items.

--
HTH

Bob Phillips

"Steph" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob. I guess that was the root of my question - How would I give
> only the Open items a Name?
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > I assumed you wanted to switch. If you only want to show the Open items,
> > give the open items a workbook name, and then add a DV with a list
linked
> to
> > that name.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Steph" <[email protected]> wrote in message
> > news:[email protected]...
> > > Wow, that's awfully complicated! I thought this would be a simple
> formula
> > > in the data validation section!!
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > See http://www.xldynamic.com/source/xld.Dropdowns.html
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Steph" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hello. I have a simple table with 2 columns. In col A, the words
> > > "Open"
> > > > or
> > > > > "Closed". In column B, a vendor. On a different sheet, I would
> like
> > to
> > > > > have data validation drop-down with ONLY the vendors that have
> "Open"
> > > next
> > > > > to them. Possible? Thanks so much!
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

that's not a dynamic range, but a static one that will have to be maintained every time the "Vendors" spreadsheet is changed