Remote Data Validation

S

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
 
J

JLGWhiz

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

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

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



ssGuru said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top