Hi,
I'm using a DATA VALIDATION list referencing a TABLE column with this formula in the SOURCE =INDIRECT("TableName[ColumnName]") - if my required cell entry isn't in the TABLE can I somehow add it automatically?
Thanks.
Hi,
I'm using a DATA VALIDATION list referencing a TABLE column with this formula in the SOURCE =INDIRECT("TableName[ColumnName]") - if my required cell entry isn't in the TABLE can I somehow add it automatically?
Thanks.
Create a named range that refers to the values in your table column, then use that named range as the source for your validation. When you add new row to your table it should update the list automatically.
Hi EchoPassenger,
What I'm hoping to achieve is this: When the DATA VALIDATION drop down list doesn't include the value required this is typed into the DATA VALIDATION cell (Switching off the error alert) and when entered this is then automatically add to either the NAMED RANGE or even better the TABLE column?
As a matter of learning I tried your suggesttion to update the SOURCE list to see changes in the drop down list but couldn't get that to work either.
Cheers.
You will need VBA to do this. Please post a small sample.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Morning John,
As suggested I've put together the sample sheet below - hopefully communicates the task in hand? Let me know if you need more info and thanks for taking a look.
Cheers.
The above code is Sheet "Before": right-click on tab and "view code"Please Login or Register to view this content.
The colours are a named range (Colours): if you change this you will need change VBA code
Adding colour should extend the named range.
Ah ok, I understand.
I'm by no means a VB expert (so others here may have a simpler method or find fault with mine) but technically you only need to use...
Please Login or Register to view this content.
But, in the case of a long list where a value might be missed, it might be better just to check that the value does not already exist before adding it.
So, add a button next to your validation cell and assign the following macro...
You may also want to add a few lines of code to sort your list in to order afterwards.Please Login or Register to view this content.
Also re. my original suggestion (I should've specified that you need to be using Excel 2007 onwards) - If you manually add a value to the bottom of you table it does then appear in your dropdown list.
Last edited by EchoPassenger; 11-25-2016 at 06:59 AM.
Hi John,
Very cool - thank you :-)
Is it possible that code can add to the TABLE rather than the NAMED RANGE, this will allow me to keep the list in Alphabetical order?
I am not a user of, nor familiar with TABLES, but changing "Colours" to "tablebefore" in the VBA appears to work OK.
I changed the DV in tab "Tableafter" to reference "tablebefore" (using you INDIRECT) and it looked OK.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
@ John, thanks - I will try that now.
@ AVK - I'm on 2016, the aim of this is to maintain a list of unique customers, in the DATA VALIDATION table the customer name may exist many times but in the reference TABLE just once - make sense?
@ EchoPassenger - thanks - I will try your suggestions too.
Hi John,
Indeed that works well - thanks - code copied below for others who are following along.
Now, if my DATA VALIDATION cell is actually a TABLE column, how would I re-write this line: If Target.Address <> "$E$7" Then Exit Sub .... this is my non-working attempt: If Target.Address <> Range "Table3[Data]" Then Exit Sub
Try
If Target.column <> x Then Exit Sub
where x is the column number
Column A=1, B=2 etc
Last edited by JohnTopley; 11-25-2016 at 10:28 AM.
Thanks again John - that seems to work just fine - I will play around to see if I can get this to work with a structured reference.
Would it be OK to ask another question which have become apparent?
The Referenence TABLE is updating nicely and set to sort Alphabetically but it seems to need an update each time a new item is added - would you know how to do this in VBA at the end the function?
This would require the user to trigger a macro at then end of "data entry" (using a button).
Presumably this requires looping through the TARGET column and identifying values which are not currently in the list.
What is wrong with current process (given my lack of knowledge of tables!) ?
Post a sample file illustrating the need.
I've attached the working demo sheet, I have the main colour list sorted Alphabetically and then added Aplha and Fushia which appear at the end of the TABLE which as you can see don't appear alphabetically until the list is sorted again 'manually'.
Try attached ...
Please Login or Register to view this content.
FAB - That works just great, you're a star.
I feel bad asking another question.... last one?.... how does one de-select the reference table and return the focus to the entry cell at the end..... to cap it off nicely :-)
Add this line before Errhandler
Target.Offset(1, 0).Select
ErrHandler:
Thanks John,
This is how it looks now but there is no change to the selection or cell focus?
Please Login or Register to view this content.
Works for me: see attached.
Enter new colour and focus is following dropdown cell
Hi John,
This was of course a 'daft user' error on my part - I spliced the code into the wrong Module! The final code is copied below.
Anyway, this is truly fantastic and your time/help is very much appreciated - hopefully of use to others too.
Many thanks and have a great weekend.
Cheers.
Please Login or Register to view this content.
Hello again John,
Sorry, slightly premature in my SOLVED!.... when I ported this to my worksheet I broke it... I think because my reference TABLE [tableBefore] is actually on a different sheet in my case - how would I take care of this imortant detail in the code?
Thanks again.
We will have to separate the code.
Post A TRUE example of your workbook and I'll look tomorrow.
Thanks John,
Here it is, have a good evening - cheers
Please Login or Register to view this content.
This code goes in GENERAL module
Alt+F11 to open VB EDITOR
Click "Insert"==>"Module"
Copy/paste code
Please Login or Register to view this content.
Morning John,
Many thanks for this, it works very nicely and is very much appreciated.
I think the only question is how to update the code to shift the focus back to the entry cell as: Target.Offset(1, 0).Select doesn't seem to work?
Amend code;
Worksheets("Data Entry").Activate
Target.Offset(1, 0).Select
I must admit adding to DV seems to defeat the object of DV as there does not appear to be validation of the entered data!
Thank you again - is it possible to deselect the "ref" TABLE at end of the function?
Add
Range("A1").Select
before "End Sub" in "Update_List"
Ah yes - I was thinking about deselecting rather than selecting just one cell!
Thanks again, I've learnt alot.
Appreciated.
Interestingly there isn't (as far as I am aware) a "deselect" option in VBA so selecting a cell appears to be the easiest way to achieve the same.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks