Advanced cell validation help needed pls
Hello
I have been successful combining IF statements with Validation rules so that the value in one cell determines what the Validation list is in another cell. eg. If cell A1="Weekday", the validation list for B1 is (Mon, Tue, Wed, Thr, Fri) If cell A1="Weekend", the validaion list for B1 is (Sat, Sun) But changes can be made to cell A1 that make the value in B1 now invalid. eg. If I select A1="Weekend", then B1="Sat", if I now change A1="Weekday", B1 will still stay as an invalid entry "Sat". I know I can use the Audting toolbar to "Show Invalid Entries" to circle all invalid entries, but it too is NOT dynamic. eg. It will not automatically remove the red circle from cells when you go ahead and choose a valid entry. You need to press the "Circle Invalid Data" button each time you make a correction to check if the data is now valid. In the case where cell A1 is changed making the value in cell B1 invalid (like in the example above), I want cell B1 to automatically/dynamically change from being an invalid entry to a value such as "<select value", prompting the user to now select a valid entry from the pull down list. Can this be done? Regards Bullman |
Advanced cell validation help needed pls
Hi!
To do EXACTLY what you want will require VBA code. You can get ALMOST the same thing using conditional formatting to hide the invalid days in B1 and then a simple formula in C1 that says "Select value" (or whatever you want it to say). Here's how: Make a list of the weekdays somewhere. I'll use H1:H7 as an example: H1 = Mon H2 = Tue ... ... H7 = Sun Select cell B1. Goto FormatConditional Formatting. Condition 1 Formula Is: =AND(A1="Weekday",OR(B1=H6:H7)) Click the format button Set the font color to be the same as the background color. OK Click Add Condition 2 Formula Is: =AND(A1="Weekend",OR(B1=H1:H5)) Click the format button Set the font color to be the same as the background color. OK your way out. Enter this formula in cell C1: =IF(AND(A1="Weekday",OR(B1={"sat","sun"})),"< Select Value ",IF(AND(A1="Weekend",OR(B1={"mon","tue","wed","t hur","fri"})),"< Select Value ","")) Biff "Bullman" wrote in message ps.com... Hello I have been successful combining IF statements with Validation rules so that the value in one cell determines what the Validation list is in another cell. eg. If cell A1="Weekday", the validation list for B1 is (Mon, Tue, Wed, Thr, Fri) If cell A1="Weekend", the validaion list for B1 is (Sat, Sun) But changes can be made to cell A1 that make the value in B1 now invalid. eg. If I select A1="Weekend", then B1="Sat", if I now change A1="Weekday", B1 will still stay as an invalid entry "Sat". I know I can use the Audting toolbar to "Show Invalid Entries" to circle all invalid entries, but it too is NOT dynamic. eg. It will not automatically remove the red circle from cells when you go ahead and choose a valid entry. You need to press the "Circle Invalid Data" button each time you make a correction to check if the data is now valid. In the case where cell A1 is changed making the value in cell B1 invalid (like in the example above), I want cell B1 to automatically/dynamically change from being an invalid entry to a value such as "<select value", prompting the user to now select a valid entry from the pull down list. Can this be done? Regards Bullman |
Advanced cell validation help needed pls
Awesome solutions guys! :D
Biff, I think your solution is probably the best that can be done without any VBA assistance. I was toying with a similar concept but your use of a Conditional Format to make the cell appear to be blank (and hence act as a kind of flag/prompt for the user to then go and fill it in with data) is a very clever work around I hadn't considered. Ardus, your use of VBA certainly provides a 100% solution to the problem. I have had a look at the code for that script that automatically resets cell B1 to <select vale if changes to A1 make the current value in B1 invalid. Unfortunately my lack of VBA code knowledge prevents me from fully understanding the code used. I wish to at least obtain a better undertanding of the VBA code used in the script so I can modify and adapt the one you wrote and apply it to other cells (with different conditions) on the same worksheet and on other worksheets. Can you perhaps suggest a good starting point? Sincerest thanks! Bullman |
Advanced cell validation help needed pls
Hi!
You can use a similar event procedure on OTHER sheets but you can't have more than 1 per sheet. OTOH, you can use similar conditional formatting techniques to your hearts content! Biff "Bullman" wrote in message oups.com... Awesome solutions guys! :D Biff, I think your solution is probably the best that can be done without any VBA assistance. I was toying with a similar concept but your use of a Conditional Format to make the cell appear to be blank (and hence act as a kind of flag/prompt for the user to then go and fill it in with data) is a very clever work around I hadn't considered. Ardus, your use of VBA certainly provides a 100% solution to the problem. I have had a look at the code for that script that automatically resets cell B1 to <select vale if changes to A1 make the current value in B1 invalid. Unfortunately my lack of VBA code knowledge prevents me from fully understanding the code used. I wish to at least obtain a better undertanding of the VBA code used in the script so I can modify and adapt the one you wrote and apply it to other cells (with different conditions) on the same worksheet and on other worksheets. Can you perhaps suggest a good starting point? Sincerest thanks! Bullman |
Advanced cell validation help needed pls
Hello Biff,
Ahh, I see. So the VBA solution really is only good for one case (cell) on the spreadsheet. Bugger. I really need it to be applied to numerous cells/instances and cases on the same worksheet. No problems, I will then use your crafty solution instead. :D Thanks again Bullman Biff wrote: Hi! You can use a similar event procedure on OTHER sheets but you can't have more than 1 per sheet. OTOH, you can use similar conditional formatting techniques to your hearts content! Biff |
Advanced cell validation help needed pls
So the VBA solution really is only good for one case
(cell) on the spreadsheet. No, it can be used for a range of cells like A1:10. That would be 1 event procedure that covers that range. But you can't have another event procedure that does something else on some other range. Hopefully, Ardus will respond. I'm not too good with VBA but I'm pretty strong with formulas! Biff "Bullman" wrote in message oups.com... Hello Biff, Ahh, I see. So the VBA solution really is only good for one case (cell) on the spreadsheet. Bugger. I really need it to be applied to numerous cells/instances and cases on the same worksheet. No problems, I will then use your crafty solution instead. :D Thanks again Bullman Biff wrote: Hi! You can use a similar event procedure on OTHER sheets but you can't have more than 1 per sheet. OTOH, you can use similar conditional formatting techniques to your hearts content! Biff |
Advanced cell validation help needed pls
Here is a fully commented version with multiple cells :
http://cjoint.com/?gcj21u5GWc You can modify the constant (eg Const sWatch = "A1:A10,E1:E10") to suit your needs. HTH -- AP "Biff" a écrit dans le message de news: ... So the VBA solution really is only good for one case (cell) on the spreadsheet. No, it can be used for a range of cells like A1:10. That would be 1 event procedure that covers that range. But you can't have another event procedure that does something else on some other range. Hopefully, Ardus will respond. I'm not too good with VBA but I'm pretty strong with formulas! Biff "Bullman" wrote in message oups.com... Hello Biff, Ahh, I see. So the VBA solution really is only good for one case (cell) on the spreadsheet. Bugger. I really need it to be applied to numerous cells/instances and cases on the same worksheet. No problems, I will then use your crafty solution instead. :D Thanks again Bullman Biff wrote: Hi! You can use a similar event procedure on OTHER sheets but you can't have more than 1 per sheet. OTOH, you can use similar conditional formatting techniques to your hearts content! Biff |
Advanced cell validation help needed pls
Thank you both again!
Ardus, being able to apply it to the same cell numerous instances like you have shown is certainly what I asked for, as I thought that there would be no limitations on having a similar VBA script to do the same thing but for a different range of cells/conditions on the same sheet. eg. If I now asked for another cell (or column of cells) (eg. E1) on the same sheet to have the validation list that is "Autumn, Winter, Spring, Summer" and next to it a cell (eg F1) whose validation list is dependant on E1 such that: if E1=Autumn, validation list is "Mar, Apr, May" if E1=Winter, validation list is "Jun, Jul, Aug" if E1=Spring, validation list is "Sep, Oct, Nov" if E1=Summer, validation list is "Dec, Jan, Feb" will a similar VBA code work to "self correct" F1 (like you have done with the days of the week example) if cell E1 is ever changed hence making F1 invalid? Or with many other cases? I ultimately am working on a spreadsheet where several field columns need to be filled out (from left to right) for each row record. The choices being made in cells to the left of each cell dictate the options of choices for the cells on the right of that cell. However, I am trying to work in the "self correcting" smarts so that if ever a particular column field in a particular row record is changed, any now invalid entries in that row and to the right of that column revert back to a <select value entry rather than remain invalid without appearing so to a user. I have been using very advanced versions of Biffs suggestions, where very complex Conditional Formatting formulas give visual cues to the user (making the cell appear to be "blank", without actually changing the value of course) that indicate that the current data entry in that cell is invalid based on the various/numerous combinations of column enrties to the left of it, prompting them to re-select a valid entry from the pull down list. I have got it all working but it has been tedious. I will be happy to share my work with you if you are at all interested in what I have been able to achieve and how I achieved it. Bullman |
All times are GMT +1. The time now is 01:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com