results from Validated cells (Excel 2003 SP3)

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

results from Validated cells (Excel 2003 SP3)

Post by 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.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: results from Validated cells (Excel 2003 SP3)

Post by 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.
Best wishes,
Hans

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: results from Validated cells (Excel 2003 SP3)

Post by 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.
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: results from Validated cells (Excel 2003 SP3)

Post by 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.
Best wishes,
Hans

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: results from Validated cells (Excel 2003 SP3)

Post by 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
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: results from Validated cells (Excel 2003 SP3)

Post by 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...
Best wishes,
Hans