Jan 15 2020 08:50 AM
I am probably not using the correct terms, but essentially I am trying to have a dropdown list in a cell. I know how to do this. I want it to display the first choice in the dropdown list until the user selects a different one. Essentially it's a default response that is populated in the cell until user selects to change it from the dropdown. Thanks.
Jan 15 2020 09:11 AM
SolutionYou could have the validated cell point at the first cell in the list. Suppose your list populating the dropdown starts at A6, you could enter:
=$A$6
....in each cell that requires validation. Example attached.
Jan 15 2020 09:39 AM
@Riny_van_Eekelen Thank you. Simple and effective. One more question you may know. How do I get this to persist as I add rows with new customers?
Jan 15 2020 09:46 AM
Not sure I follow. You mean that you want to expand the range that populates the drop down?
Jan 15 2020 10:27 AM
@Riny_van_Eekelen I have a long list of customers. I was successfully able to add your solution to the existing rows. Thank you! The challenge I am having now is when I add a new row/customer to my list, which is in table format. The =$A$6 doesn't copy into the cell when I add the new row. I realize this is an issue unrelated to the initial question, but something I will need to solve. Thanks.
Jan 15 2020 12:01 PM
Not sure I follow. Are you inserting above A6? Then the cell under your drop down will point A7, despite the dollar signs.
Jan 15 2020 12:14 PM
I am adding new rows at the bottom. Essentially I have a worksheet of customers. I have created dropdown lists for different columns, such as region and industry. I made this worksheet into a table so as I add a new row/customer, the table design would continue and ideally so would the dropdown lists. The table design does continue/expand......however, the cells that should have the dropdown lists do not, and the "=A$6" does not either. They are empty - the dropdown lists and "=A$6" did not follow. The question goes beyond the "=A$6".........it's more about properly converting my worksheet into a table so formulas and dropdown lists will continue as I add new rows/customers at the bottom. Thanks.
Jan 15 2020 12:21 PM
@JimS-2145 Difficult to resolve without the worksheet "in-hand".
Jan 15 2020 05:54 PM
@Riny_van_Eekelen I went back and recreated all of my tables and validations and now it works. Your solution for creating a default answer was perfect and a lot easier than other visual basic solutions I've read. Thanks.
Jan 15 2020 09:39 PM
You're welcome. Glad you figured it out yourself!
Mar 04 2021 06:57 PM
Mar 04 2021 07:15 PM
Mar 04 2021 09:43 PM
@jefort You'll need a small piece of VBA in the worksheet code for that. Now, I'm not a VBA expert, so I just picked-up an example from the web https://www.automateexcel.com/vba/worksheet-change-event/ and adapted it to your schedule.
I have also added some formulae (in columns P and Q) that will work if you are on a modern Excel version that supports the use of UNIQUE and FILTER. Two functions that will be much easier to write, understand and maintain than what you currently have in column M.
Now, if you prefer not to download and open a macro enabled workbook, follow the instructions in the above-mentioned link and paste the following code in the code area for Sheet1.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$7" Then
Range("G8").Formula = "=M7"
End If
End Sub
Mar 05 2021 12:59 AM
WOW. Thanks a LOTTTT. It worked.
I've been searching the web on how to do this and here it is fixed already.
Thank you. Thank you.
Mar 11 2021 09:21 PM
Mar 11 2021 10:19 PM
@jefort I guess that would work. Give it a try. As said, I'm not very much into VBA. Perhaps someone else here can step in.
Mar 11 2021 11:04 PM
Just an observation, but you have a single apostrophe included in the $H$4 string that I think will cause that test to fail:
Jan 15 2020 09:11 AM
SolutionYou could have the validated cell point at the first cell in the list. Suppose your list populating the dropdown starts at A6, you could enter:
=$A$6
....in each cell that requires validation. Example attached.