Data validation with dependent dropdowns from a sorted list - seperate document?

[MrSteve]

Hello

I am currently using this system for my data validation with dependent dropdowns from a sorted list:

http://www.contextures.com/xlDataVal13.html

As per the example above, the keywords used in the lists are in a seperate tab.

I was wondering if anyone would know how I can put the keywords in a seperate document (for example, somewhere on the network) and still have this work ok?

Attached is the document I use.

Thank you very much.

[Bob Phillips]

Of course you could, but you would be adding quite a hefty overhead in having to open the keywords file, and reading it in.

[MrSteve]

Thank you for your reply.

We have a weird setup here so we need the keywords in a different document unfortunately.

Am I right in thinking I can simply change (for example):

=IF(C2="",RegionList,INDEX(RegionColumn,MATCH(C2,CustColumn,0)))

to

=IF(C2="",Z:\Keywords.xls:RegionList,INDEX(Z:\Keywords.xls:RegionColumn,MAT CH(C2,CustColumn,0))) or something like that?

Thank you

[mdmackillop]

I would suggest you either have Keywords.xls open, in which case Z:\ would not be correct, or to open it, import the values and use the imported data.

[MrSteve]

Thank you for your reply.

Do you mean on my PC I will have two spreadsheets open:

A. The spreadsheet which uses the keywords
B. The spreadsheet with the keywords

And it should work fine?

How will spreadsheet A know to look in spreadsheet B?

Sorry if I'm being stupid...

Thanks

Steve

[MrSteve]

I have been thinking about this more. If I change the path of the keyword columns in the "Insert -> Name -> Define" section to the keywords spreadsheet on the network, would this do the trick?

Thanks

[mdmackillop]

Create you formulae as usual with your server workbook open. Add code to open the server workbook when your main book is opened. Such as the following (placed in ThisWorkbook module)
[VBA]
Private Sub Workbook_Open()
Dim wbSource As Workbook
Dim wb As Workbook
Set wb = ThisWorkbook
Set wbSource = Workbooks.Open("C:\AAA\Book1.xls")
wb.Activate
End Sub

[/VBA]

[mdmackillop]

Do you mean on my PC I will have two spreadsheets open:

You would have two Workbooks open.

[MrSteve]

That worked perfectly.

Thank you very much for your help!