making a non-validation list

[tjb]

OK I need an in-cell drop down (or even a combo box would do...) with values
populated from another workbook that resides on a public server.

Using validation only lets me have an in-cell drop down list with values
populated from a range of cells on the same sheet in the same workbook.

Basically, what I'm doing is my users will have the sheet with the in-cell
drop down list and I want to dynamically control what they see in their drop
down list. I can dynamically control a VLOOKUP using this method but not
their lookup value for the VLOOKUP.

[Bob Phillips]

You can use a named range or the INDIRECT function
(=INDIRECT("Sheet8!A1:A10")) to refer to a range on another sheet.

--

HTH

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


"tjb" <tjb@discussions.microsoft.com> wrote in message
news:7841F39B-B31D-4387-942A-2B7FA4EBFA32@microsoft.com...
> OK I need an in-cell drop down (or even a combo box would do...) with
values
> populated from another workbook that resides on a public server.
>
> Using validation only lets me have an in-cell drop down list with values
> populated from a range of cells on the same sheet in the same workbook.
>
> Basically, what I'm doing is my users will have the sheet with the in-cell
> drop down list and I want to dynamically control what they see in their
drop
> down list. I can dynamically control a VLOOKUP using this method but not
> their lookup value for the VLOOKUP.

[Bernie Deitrick]

tjb,

Use formulas to pull the list onto the same sheet, and use the range with
the formulas as your validation list.

HTH,
Bernie
MS Excel MVP

"tjb" <tjb@discussions.microsoft.com> wrote in message
news:7841F39B-B31D-4387-942A-2B7FA4EBFA32@microsoft.com...
> OK I need an in-cell drop down (or even a combo box would do...) with
> values
> populated from another workbook that resides on a public server.
>
> Using validation only lets me have an in-cell drop down list with values
> populated from a range of cells on the same sheet in the same workbook.
>
> Basically, what I'm doing is my users will have the sheet with the in-cell
> drop down list and I want to dynamically control what they see in their
> drop
> down list. I can dynamically control a VLOOKUP using this method but not
> their lookup value for the VLOOKUP.

[tjb]

I'm trying to get this to work but it's not going through. The range is
located on a sheet in a workbook located on a SharePoint server that will be
accessed by all of my users. So the code I have in the validation looks
something like this:

=INDIRECT("http://sharepoint/Payroll/interim_data/interim_data.xls'!A1:A100")

"Bob Phillips" wrote:

> You can use a named range or the INDIRECT function
> (=INDIRECT("Sheet8!A1:A10")) to refer to a range on another sheet.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "tjb" <tjb@discussions.microsoft.com> wrote in message
> news:7841F39B-B31D-4387-942A-2B7FA4EBFA32@microsoft.com...
> > OK I need an in-cell drop down (or even a combo box would do...) with
> values
> > populated from another workbook that resides on a public server.
> >
> > Using validation only lets me have an in-cell drop down list with values
> > populated from a range of cells on the same sheet in the same workbook.
> >
> > Basically, what I'm doing is my users will have the sheet with the in-cell
> > drop down list and I want to dynamically control what they see in their
> drop
> > down list. I can dynamically control a VLOOKUP using this method but not
> > their lookup value for the VLOOKUP.
>
>
>

[Bob Phillips]

Sorry, it works with another worksheet, but not another, closed workbook.

--

HTH

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


"tjb" <tjb@discussions.microsoft.com> wrote in message
news:FF29D47B-C440-49AC-8799-8E4BA229B7AD@microsoft.com...
> I'm trying to get this to work but it's not going through. The range is
> located on a sheet in a workbook located on a SharePoint server that will
be
> accessed by all of my users. So the code I have in the validation looks
> something like this:
>
>
=INDIRECT("http://sharepoint/Payroll/interim_data/interim_data.xls'!A1:A100"
)
>
> "Bob Phillips" wrote:
>
> > You can use a named range or the INDIRECT function
> > (=INDIRECT("Sheet8!A1:A10")) to refer to a range on another sheet.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "tjb" <tjb@discussions.microsoft.com> wrote in message
> > news:7841F39B-B31D-4387-942A-2B7FA4EBFA32@microsoft.com...
> > > OK I need an in-cell drop down (or even a combo box would do...) with
> > values
> > > populated from another workbook that resides on a public server.
> > >
> > > Using validation only lets me have an in-cell drop down list with
values
> > > populated from a range of cells on the same sheet in the same
workbook.
> > >
> > > Basically, what I'm doing is my users will have the sheet with the
in-cell
> > > drop down list and I want to dynamically control what they see in
their
> > drop
> > > down list. I can dynamically control a VLOOKUP using this method but
not
> > > their lookup value for the VLOOKUP.
> >
> >
> >