Remote Data Validation

[ssGuru]

What choices do we have to get around Excel's refusal to use a remote
workBOOK for cell data entry validation?

No problem to use another internal workSHEET when the range is named
but can't use an EXTERNAL workbook.

Could I perhaps use an internal hidden worksheet that obtained its
data from a remote workbook?
Still working on solutions.

Any ideas would be appreciated.

Dennis

 

[JLGWhiz]

When I have those kinds of problems, I usually find a workaround that falls
withing the design parameters of the system software.

 

[Ron Coderre]

See Debra Dalgleish's website for some ideas.....start here:
http://www.contextures.com/xlDataVal05.html

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Dave Peterson]

Visit Debra Dalgleish's site:
http://contextures.com/xlDataVal05.html

 

[Tom Ogilvy]

Just to answer you own thinking -
Your idea of using a hidden sheet will work. The links in the cells of the
sheet to the other workbook will work like any other links to get the data
into the local workbook, then you can use that local copy for your data
validation using defined names.

Like any normal in cell straight link, they work whether the other/source
workbook is open or closed.

='C:\My folder\[Myfiles.xls]Sheet1'!A5

no space in the string, no need for single quotes.

 

[ssGuru]

Just to answer you own thinking -
Your idea of using a hidden sheet will work. The links in the cells of the
sheet to the other workbook will work like any other links to get the data
into the local workbook, then you can use that local copy for your data
validation using defined names.

Like any normal in cell straight link, they work whether the other/source
workbook is open or closed.

='C:\My folder\[Myfiles.xls]Sheet1'!A5

no space in the string, no need for single quotes.

--
Regards,
Tom Ogilvy

What choices do we have to get around Excel's refusal to use a remote
workBOOK for cell data entry validation?

No problem to use another internal workSHEET when the range is named
but can't use an EXTERNAL workbook.

Could I perhaps use an internal hidden worksheet that obtained its
data from a remote workbook?
Still working on solutions.

Any ideas would be appreciated.

Dennis- Hide quoted text -

- Show quoted text -

Thats great Tom, I may give that a try if nothing better comes along.
Only thing I could think of. Just seems like a lot of redundancy so I
can manage frequent hidden lookup table and list updates.

The goal of housing my lookup tables in another workbook is to
facilitate template updates. My formulas work just fine pointing to
another workbook in the same folder. No special path needed. Just
change the reference to include the file name.

BUT I then was faced with the data validation problem and needed a
workaround.

Would like to hear of any other concepts or code ideas that will
resolve the remote data validation problem.

Thanks all for any input,
Dennis