Example Attached:
I have set up a data vlidation & then a dependant validation.
What I now want to do is:
When I select a different initial Data Vaidation value
that the cell which picks up the Dependant Validation value - goes blank or throws an error or something
until I select the appropriate dropdown.
This is to ensure that the appropriate Head & Sub are always used.
The solutions I have found online are all based on VBA - which I know nothing about.
Thank in Advance
THe structure and layout of a sheet like this has a great impact on it.
1) I've moved your lists into a Lists sheet
2) I've given row1 a dynamic named range call Days
3) This sheet is dynamic, you can add as many additional columns as you wish and add as many items IN each column as you want.
4) On the Form sheet, I've used Days as the Data Validation source for column A
5) In column B instead of INDIRECT() we are using an OFFSET() formula in the DV settings to do all the work. This formula is spotting the day selected in column A on the LISTS sheet to find which column it is in, then offsetting down to row2 of that column and then expanding downward to encompass all the entries. So it's dynamic, the second list creates itself.
This solves the dependency issue, this will run itself consistently no matter how much you expand the columns and rows on the LISTS sheet.
================
Then I applied conditional formatting to column B to turn the font in that cell white if you change a column A value and the B value no longer matches that lists.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
JBeaucaire
Thank you your instructions & examples were excellent.
I did have to modify a little as I set the example up differently to my actual file.
All went well with the exception of the Conditional Formatting &
I wonder if you could again assist.
It worked on some but not all and I think the issue is coming from the fact that I have a different number of fields than I had shown in my example.
Where in my example I had
7 Days - my doc has 32 options
Max 3 tasks (under my days) - my doc has up to 64 options
I tried to adjust the Offset formula but having never used it the Rows, Columns, Height & Width thing confused me & the variations I put in didn't work.
Could you please give me a step for a hint?
Thanks Again
Just setup your lists exactly as I have shown on a LISTS sheet, then tweaking the formula takes little adjustment. I didn't see a workbook if you needed me to look at something. If you do post it, I'm going format the lists as I've shown, so you might as well do it. Exactly as I laid it out starting in A1 on a LISTS sheet.
JBeaucaire
I'm sorry if perhaps I am doing something wrong here on the conditional formatting. I think it might be on the Offset Formula.
I have attached another file which is set up exactly as my Actual documnet (except of course for the names etc - you might guess these are dummies to suit) but it's the exact number of fields in my document.
I have also attached examples of the mistakes to try & assist.
I grealty appreciate your help.
When I opened your sheet and changed the country in A2, the city A3 disappeared as the CF painted it white. It's working great.
http://screencast.com/t/H2kfnOkhw
Last edited by JBeaucaire; 02-20-2013 at 03:19 AM.
Thanks for your video.
I have used your CF formula showing True/False and Yes many of the combinations work however I am still having issues.
I checked my lists just in case and Boomerang Rd def only appears in Australia.
If when the country is Canada - I pop onto A3 (where it says Boomerang Rd) the drop down only shows those laces associated with Canada,
it just doesn't turn Boomernag text white when these particular countries are selected.
Could you perhaps try these specific examples just incase I am doing some thing wrong?
I really do appreciate your help.
How they are set on my actual doc *Changed range name to Country*
Canada Day(Country)
Boomerang Rd FALSE Task
Example of Error:
Correct Australia
Correct Boomerang Rd
Correct Change country to Barbados - Boomerang Rd vanishes (text goes white)
Error Change country to either Canada, Dominica, Finland, Germany, Sweden, Turkey, Angola, - Boomerang Rd pops up again (text goes black)
Bah! I left the MATCH's last parameter off, which makes it default to "fuzzy" matches. My bad.
=NOT(ISNUMBER(MATCH($A3,OFFSET(Country,1,MATCH($A2,Country,0)-1,100,1), 0)))
NOTE: Some of your lists are pretty long, you might want to increase the 100 to 1000.
Last edited by JBeaucaire; 02-20-2013 at 10:46 AM.
Works perfectly.
Thank you so much.
Your initial response was a solution.
My specific needs for this task were a little different - your patience in looking at my particular issue again to get it 100% was greatly appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks