results from Validated cells (Excel 2003 SP3)

[steveh]

Morning all

I realise that what I am probably trying to achieve is probably more suited to a DB environment but being more Access challenged than Excel challenged (if that is possible) I am using Excel. Our board in there infinite wisdom have choosen a software developer that knows nothing about our industry but to whom I must impart lots of information by Monday. I can draw it and make PP presentations to describe it but they are insisting on some sort of working models.

To try and demonstrate our booking system requirement I have made 3 dependent validation boxes which filters information as fololows

Account - the validation shows 8 sample accounts
Study - Each account (for this demo) has 8 studies that are associated with it and is filtered once the selection from Account has been made.
Site - Each study (for this demo) has 8 sites associated with it that have a complete 2 and from address and contact details and is again filtered by the selection from, Account ---- Study ----- Site

Once the Site name has been selected from the 3rd validation I am pulling in the complete name and addresses into several name, address, City, Country, Post code fields for example using this formula =IF($D$2="","",VLOOKUP($D$2,Labco1a,2,FALSE) and changing the highlighted number in each cell to reflect that portion of the address.

This works fine when I select 1st Account, 1st Study and am presented with a list in the 3rd validation of Site-1 through Site-8 each site I select changes the to and from fields.

My problem is that if I now select 1st Account, 2nd study I am presented with all of the sites that are associated with it but selecting one of them produces N/A in all of the address fields. I realise that this is because the formula is specifically pointing towards Labco1a and because I have selected study 2 that has changed. Are there any quick thoughts on what I can do in the formula to allow for the change in validation 2?

I know that an attachment would be useful but I am not home right now and the workbook I have built is jammed with propriatry names in all of the named ranges etc. and I will try and make an example to post, I thought however somebody might see a quick fix from the description.

[HansV]

You'll have to change the definition of the named range Labco1a so that it varies according to the selected items.
But without seeing the workbook it's impossible to provide specific instructions.

[steveh]

Hi Hans

Thanks for the response. I have now removed a lot of the data and changed the propriatry names to industry standard names. Hopefully the WB will make sense, I am off to give myself a crash course in basic Acces to see if I can do anything else with this structure.

[HansV]

If you want to know how to do this in Access, see How to synchronize two combo boxes on a form in Access 2002 or in Access 2003 and this post in the Windows Secrets Lounge.

[steveh]

Hi Hans

Thank you for the pointers, my level of knowledge (or lack of) though has left me in the same spot. I tried to download the example in the second link but whatever I do it says it is a read only.

I will try and implement the excel version by the morning but thanks once again for the pointers. When I have my next break from work I will seriousely have to have a look at Access

[HansV]

The database is in Access 97 format, you have to convert it to Access 2000 format (or to Access 2002/2003 format) to be able to change its design.
To convert: select Tools | Database Utilities | Convert Database | To Access 2000 Format...