Hello everyone. I am trying to add a list to a cell using data validation. What I am trying to accomplish is basically: if cell A1 equals 1 then show list, otherwise show blank (or no list will be shown), however I cannot get it to work that simply. What I have tried is adding a data validation using custom and then including the following formula: =IF($A$1="1",List,""), however this does not work. If this is possible, could someone please help me with the proper method of doing this?
Last edited by tm1274; 07-07-2021 at 08:33 AM.
Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
Also add your excel version to your profile please
Hello Pepe Le Mokko, Thank you for your reply. I have made the requested changes and attached the sample file.
In DV Settings allow LIST (not custom) and use:
=IF($A$1=1,List,"")
The "" around the 1 turns it into text.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thanks Glenn, That worked perfectly. I never realized that the "" turned it into text. That explains why my original use of allow list instead of custom didn't work and returned an error that the list needed to be delimited.
Try this
Cell D1
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
I just realized that the formula =IF($A$1=1,List,"") does not actually make the cell D1 blank if anything but 1 is selected in A1. Is it possible to make cell D1 (where the list is shown) blank if the value in A1 is anything but 1?
I have updated my sample sheet to hopefully explain better what I am looking to accomplish. Does anyone know if clearing the cell value when a selection is made is possible using the data validation?
With data validation is not possible, only with VBA as a event on change to the worksheet.
I am using Excel 2019 and I don't remember if this is working in 2003 worksheetsHTML Code:
Diana Tanase
If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
I was hoping that the data validation would do it. The VBA did work in 2010 when I tested it. Thanks for your reply.
Hello tanasedn. Your response worked great but I am wondering if you could tell me, is it possible to set a second range that would clear other cells? I have tried the following but with no real VBA experience, I am having trouble figuring out what I am doing wrong. Would it need a completely different Worksheet_Change event to clear other cells with a different range?:
Please Login or Register to view this content.
Last edited by Glenn Kennedy; 07-09-2021 at 11:21 AM.
What you really want to do? Not how you try to do it. I don't understand what is the connection between A1, D3 and G column
I apologize. A1 is no longer being used as the reference range as it was originally. It is now D3. What I have currently working is if D3 equals less than 1 then cell G1 would be blank and then if D3 equals less than 2 then cell G2 would be blank and so on. What I am trying to do is add another range in the code such as G1, if it is < 1 then cell C12 would be blank.
Tm1274, please take a moment to re-read Rule 2 about code tags. I have added them this time, at Post 12.
Please, please, please attach a sample file!Originally Posted by tm1274
Sure. Let me create one without any critical data in it and I will post it.
Hopefully this example workbook will help explain what I am trying to accomplish. Please let me know if you have any other questions.
Thanks Glenn. I apologize for that mistake. It has been quite a long time since I had last posted and need a refresher on the forum rules. Looking at them now.....
tm1274... no problem!! I'll ask you questions about them tomorrow
Incidentally, are you still using Excel 2010? If not, can you update your profile? It would also help if you provided a rough indication of where you are...as solutions offered can depend on both Excel version and national location.
Yes, still using Excel 2010. I added my location as well. Looks like the last time I was on the forum was 2014, so it's been a couple days.
Last edited by tm1274; 07-09-2021 at 02:30 PM.
I worked a little on your problem and I came to a result close to what you want. However, I did not manage to build the code in such a way that when in D3 you return to the value 0, all the previous elements are deleted. This is all that I can for the moment.
HTML Code:
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks