Add to Data Validation Values From Data Entry Cell

[Ryusui]

Another request.

Hopefully this makes sense, but I want to know if there is a way to add to the values of a Data Validation table directly from the cell that is referencing that table, instead of having to manually go to the Data Validation range and add the new values there.

For example:

• User enters data on sheet "SUMMARY" between B7 and B36
• B7:B36 have drop-down lists referencing Column A in "TABLE1" on sheet "Values"
• "TABLE1" has data in columns B & C
• When a value is chosen in B7:B36, the corresponding cells in column C & D auto fill using the data that corresponds to the cells in B & C from the value selected from A in "TABLE1"
• Let's say "TABLE1" has values of 111, 222, 333, 444 in A; COMPANY, COMPANY2, COMPANY3, COMPANY4 in B; COMPANY DBA, COMPANY DBA2, COMPANY DBA3, COMPANY DBA4, in C
• Error alerts are turned off, so the user can input their own values if desired

What I'd like to know, is if the user enters their own values into B, C, and D on "SUMMARY" - let's say they put 789 into B7, COMPANY NAME5 in C7, and COMPANY DBA5 in D7 - is there a way for those three new values to be automatically added to TABLE1? So when they move down to B8, B9, etc., their drop-down list now has 111, 222, 333, 444, and 789 - and if they choose 789 it will be able to pull the new values from TABLE1 that were added from C7 and D.

Thanks for any time and assistance given to my request! And I hope that wasn't a confusing blur of gibberish.

 

[Rijnsent]

Hi Ryusui,
I think the only way to make that work is to use VBA. There are a couple of issues you bump into, the first one I can see: when you input "789" for the first time, it's not in the list, so than you want the user to input the values in columns B and C, but the second time/row they input "789", those cells should be auto-filled with formulas, pulling in the info. So that creates a circular reference if you want to solve it with only formulas.
Best would be to run some VBA in the main sheet with an Worksheet_Change event, that should check if there is a new record (meaning: new value with hard values in column B, C and D), add that to the second table and put the default formulas back into the cells in column C & D. If you have a start with some code and need help, feel free to post it here.
Cheers,
Koen

 

[Anthony47]

What I'd like to know, is if the user enters their own values into B, C, and D on "SUMMARY" - let's say they put 789 into B7, COMPANY NAME5 in C7, and COMPANY DBA5 in D7 - is there a way for those three new values to be automatically added to TABLE1? So when they move down to B8, B9, etc., their drop-down list now has 111, 222, 333, 444, and 789 - and if they choose 789 it will be able to pull the new values from TABLE1 that were added from C7 and D

This can be done, if you accept to use data validation as a "helper" (simplify entering of data) and not a "controller" (reject wrong values)

 

[kvsrinivasamurthy]

Worksheet event code is used.
Column A in table1 header is taken as Column2 in code . Change as per your table header. It is also assumed Column A in Table1 is having numbers.
code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D7:D36")) Is Nothing Then
Application.EnableEvents = False
d = Evaluate("Match(" & Target.Offset(0, -2).Value & ", TABLE1[Column2], 0)")
If IsError(d) Then
Range(Target.Offset(0, -2), Target).Copy Sheets("Values").Range("TABLE1[Column2]").End(xlDown).Offset(1, 0)
End If
Application.EnableEvents = True
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab SUMMARY --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

 

[kvsrinivasamurthy]

Continuation of post #4
Event will be triggered when values are changed in D7:D36. If required change the range in the code.
Also do this change . Select A7:A36 in SUMMARY sheet
select data validation.
in the DV dialogue box select Error check
uncheck Show error alert when invalid data entered.