PDA

View Full Version : Data validation with dependent dropdowns from a sorted list - seperate document?



MrSteve
03-19-2008, 02:59 AM
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
03-19-2008, 03:27 AM
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
03-25-2008, 03:22 AM
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,MATCH(C2,Cus tColumn,0))) or something like that?

Thank you

mdmackillop
03-25-2008, 05:05 AM
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
03-25-2008, 05:11 AM
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
03-25-2008, 09:44 AM
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
03-25-2008, 10:59 AM
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)

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

mdmackillop
03-25-2008, 11:01 AM
Do you mean on my PC I will have two spreadsheets open:


You would have two Workbooks open.

MrSteve
03-26-2008, 08:13 AM
That worked perfectly.

Thank you very much for your help! :)