dependent cell validation

bvas

New Member
Joined
Aug 7, 2002
Messages
12
I've got two columns of dependedent validated lists. If you change the first column, it doesn't change anything to the second unless you click it. Is there any way to setup anything to maintain data consistency or at least put something there that tells the user to make another choice? Anything but the previous value would be great! Part of the problem is that changing the list selection of a cell with validation doesn't trigger any events (that I know of).

Any ideas?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

johny

Board Regular
Joined
Jul 15, 2002
Messages
69
Hi bvas
Your right, Data validation won't
work on cells that change unless you are
in the cell making changes.
Ive worked around this using conditional formatting the cell turns red if condition is false. If you give me an idea what your lists are for I can try to help. What is the list for. Does it change.
 

bvas

New Member
Joined
Aug 7, 2002
Messages
12
Well, items in each list can change, but hopefully not frequently. I'm designing an equipment list with a column of equipment type (car, for example) and another column with sub-type (ford). If the user changes the main type to something else (bicycle), I want the available sub-type column to at least do SOMETHING that indicates another action needs to be taken. I really can't anticipate how often a user would do it, but it would create a problem if it even happened once...
 

johny

Board Regular
Joined
Jul 15, 2002
Messages
69
I'll try to explain
Lets say you have a sheet called [mainpage]all the equipment types in col A and then
in B,c,d etc the answers. This would be called your [Mainpage]
on the sheet your going to change lets say its called [equipment] you can
use this code in [A170] for example
=INDEX('mainpage'!$A$1:$D$108,MATCH($A1,'mainpage'!$A$1:$A$108,0),2)
so when you put the word [CAR] in [A1] on the [sheet called equipment]
this code will look for the word in the [main
page] and then choose column 2 which is col B
and the answer lets say is [Ford]now
A170 on sheet called [equipment] = ford

FInally this is what you wanted
Now in B2 in [equipment}you can put =a170
and the answer will change automatically
I know its confusing just try it.
ANy time [A1}changes it will look for the new name and = col B in the Mainpage.
If you need help reply Thanks Johny
 

Jamewallace

New Member
Joined
May 23, 2012
Messages
1
I want the available sub-type column to at least do SOMETHING that indicates another action needs to be taken. I really can't anticipate how often a user would do it, but it would create a problem if it even happened once...

www.automateandvalidate.com
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,863
Messages
5,834,046
Members
430,257
Latest member
Todor T

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top