Відео

Private Sub Worksheet_Change(ByVal Target As Range) ' Initializing variables Dim oldVal As String Dim newVal As String ' Avoid running the event code recursively Application.EnableEvents = False ' Checking if the changed cell is within the specified range If Not Intersect(Target, Range("AA4:AA300")) Is Nothing Then ' Checking if the cell has data validation If Not Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then ' If the cell is not empty If Target.Value <> "" Then newVal = Target.Value ' Undo the recent change to get the old value Application.Undo oldVal = Target.Value ' Combine the old and new values, if the new value is not already in the old value If oldVal = "" Then Target.Value = newVal Else If InStr(1, oldVal, newVal, vbTextCompare) = 0 Then Target.Value = oldVal & ", " & newVal Else Target.Value = oldVal End If End If End If End If End If ' Re-enable events Application.EnableEvents = True End Sub

I’m exactly the same, followed the video and still nothing

This is what I used Private Sub Worksheet_Change(ByVal Target As Range) ' Initializing variables Dim oldVal As String Dim newVal As String ' Checking for changes in the drop down lists Application.EnableEvents = True If Not Intersect(Target, Range("$E$2:$E$65")) Is Nothing Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then Exit Sub ' Adding the multiple selected items to the list Else If Target.Value = "" Then Exit Sub Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value If oldVal = "" Then Target.Value = newVal Else If InStr(oldVal, newVal) = 0 Then Target.Value = oldVal & ", " & newVal '& vbNewLine & Else Target.Value = oldVal End If End If End If End If End If Application.EnableEvents = True End Sub

Hi Daniel, Yes, because the VBA code is not designed to handle those situations; here's a modified code, notice how the last part is altered: Private Sub Worksheet_Change(ByVal Target As Range) ' Initializing variables Dim oldVal As String Dim newVal As String ' Checking for changes in the drop down lists Application.EnableEvents = True If Target.Address = "$G$2" Or Target.Address = "$G$3" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then Exit Sub Else If Target.Value = "" Then Exit Sub Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value If oldVal = "" Then Target.Value = newVal Else ' If newVal already exists, remove it, otherwise add it If InStr(oldVal, newVal) = 0 Then Target.Value = oldVal & ", " & newVal Else Target.Value = Replace(oldVal, ", " & newVal, "") Target.Value = Replace(Target.Value, newVal & ", ", "") If Target.Value = "" Then Target.Value = newVal End If End If End If End If End If Application.EnableEvents = True End Sub

@@ExcelWithAnser Thanks a bunch

Hi there, ensure that the sheet names in your table of contents are the EXACT same as the actual sheet names; Spaces and special characters all count as well.

No spaces between names and sheets but the response is "reference isn't valid"

Hi there, make sure your macros are enabled (saving the file as XLSM), and that you have the correct cell references in your VBA code; Also, it may be a good idea to remove any existing formatting that was done to the cells.

Hi Jose, The VBA code is not designed to handle those situations; here's a modified code, notice how the last part is altered: Private Sub Worksheet_Change(ByVal Target As Range) ' Initializing variables Dim oldVal As String Dim newVal As String ' Checking for changes in the drop down lists Application.EnableEvents = True If Target.Address = "$G$2" Or Target.Address = "$G$3" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then Exit Sub Else If Target.Value = "" Then Exit Sub Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value If oldVal = "" Then Target.Value = newVal Else ' If newVal already exists, remove it, otherwise add it If InStr(oldVal, newVal) = 0 Then Target.Value = oldVal & ", " & newVal Else Target.Value = Replace(oldVal, ", " & newVal, "") Target.Value = Replace(Target.Value, newVal & ", ", "") If Target.Value = "" Then Target.Value = newVal End If End If End If End If End If Application.EnableEvents = True End Sub

@@ExcelWithAnser Hi, is there a Code for Deselecting, I mean if I click the same value then it should remove the value. I could not find the code for the same in any of the comments :(

Thank you!

It's my pleasure!

You're welcome, Susan.

Hi Hina, Here you go: Private Sub Worksheet_Change(ByVal Target As Range) ' Initializing variables Dim oldVal As String Dim newVal As String ' Checking for changes in the drop down lists Application.EnableEvents = True If Target.Address = "$G$2" Or Target.Address = "$G$3" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then Exit Sub ' Adding the multiple selected items to the list Else If Target.Value = "" Then Exit Sub Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value If oldVal = "" Then Target.Value = newVal Else If InStr(oldVal, newVal) = 0 Then Target.Value = oldVal & ", " & newVal '& vbNewLine & Else Target.Value = oldVal End If End If End If End If End If Application.EnableEvents = True End Sub

Hi there, In the second argument of the XLOOKUP function (Lookup Array), you can add and "&" function if you want to use multiple columns as your condition, and then in the 3rd argument of XLOOKUP, you can refer to the return array, whichever column that may be. I hope that helps. Feel free to let me know if you need further explanation!

Yes, but as long as the spacing and size is consistent throughout all sheets within your workbook, it still looks professional, in my opinion.

Hi Mohamed, I am not sure. Have you tried hiding a sheet and seeing if you can access it from the table of contents?

Never mind .... I used a lower case $d$3 instead of the capital letter "D$3".

@@kellybrickley313 Great, I'm happy to hear it worked for you!

(Taking the A column as example), substitute If Traget.Address = "$A$1" Then with If Not Intersect (Target, Range("A1:A300")) Is Nothing Then Then the code will apply for any cell between A1 and A300. It worked for me!

@@MattiaScassellati it for some reason won't do multiple selection no matter what

@@MattiaScassellati Thanks for sharing. @user-qr2ln6wx6y - Did that solve your problem?

Formula 1) =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””) Formula 2) = INDEX(ListWorksheets, A2) (remember “listworksheets is the custom formula I made in name manager, and I named it this) Formula 3) =HYPERLINK(CONCAT(“#”,B2,”!A1”), “Go to sheet”)

Yes, I apologize. Here you go: Formula 1) =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””) Formula 2) = INDEX(ListWorksheets, A2) (remember “listworksheets is the custom formula I made in name manager, and I named it this) Formula 3) =HYPERLINK(CONCAT(“#”,B2,”!A1”), “Go to sheet”)

I think you should be able to solve that with a formula, rather than Power Query. Say you have the Status in Column A (Member or Non-Member). You can simply do =countif(A:A, "Member") to see how many Members you have, and =countif(A:A, "Non-Member") to see how many non-members you have.

Thank you!

😂😢😮😅😊😊​@@ExcelWithAnser

Hi, thanks for the feedback. I will keep this in mind for next time!

Correct. I believe that's only in the web version. Thanks for sharing!

No, not the only the web version all versions of MS 365, Home, Business and Pro. However if you're not using 365 then expandable data validation lists are the way to go.@@ExcelWithAnser

Glad you found it helpful!