Add to Data Validation Values From Data Entry Cell

Ryusui

New Member
Joined
Apr 15, 2006
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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)
 
Upvote 0
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:
VBA 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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top