PDA

View Full Version : Sleeper: Update list from a ranges input



babycody
04-15-2005, 05:34 PM
Hello All,
I have a data validation list in column U. The drop down lists are applied to range H5:H801. There are new and unique entries typed into that range all the time. This is a two problem question. First is it possible to update the list permanently(not dependent on formulas) if an entry is made to range H5:H801 that isn't on the list in Column U? I want the list to grow on its own as new and unique items are entered into cells in the H5:H801 range. For the second part of my question: Would it be better to have my list in a different workbook? Then perhaps an autosave new list workbook could be made on close of my master workbook. That way I save the list without goofing up my master workbook. Perhaps you have a better suggestion altogether. This would be such a time saver to me. Thank you.

Jacob Hilderbrand
04-15-2005, 05:46 PM
What I generally do is create a worksheet dedicated to just storing validation lists. That way users can add items to that list and the validation list will be updated.

To do this you also need to create a named range for the list and have the validation list refer to that name. Then just make sure to update the range that the name refers to when changes are made.

We can add some VBA for that (Note that there is also a formula method to do this but it is complicated). Let's say the sheet name is "Sheet1" then:


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
n = Sheets("Sheet1").Range("A65536").End(xlUp).Row
ThisWorkbook.Names.Add Name:="MyList", RefersTo:=Sheets("Sheet1").Range("A1:A" & n)
End Sub

babycody
04-15-2005, 06:08 PM
The code you posted makes a list in column A named "MyList" right? I don't see where the code looks for new entries into range H5:H801 and adds them to the list in column U if they aren't on the list. I am trying to get away from adding new information to the list manually. It would be simular to doing a vlookup for the value in H5 and the table would be in column U. If the vlookup didn't find a match then the value in H5 would be added to the end of the table(list) in column U. Of course you can't do this with formulas permanently. I am hoping that I can hard code it in.

Found it here http://www.ozgrid.com/Excel/excel-validation-list-update.htm

byundt
04-16-2005, 10:57 AM
Here is an array formula I use to create lists of unique values contained in another list:


=""&INDEX(List,SMALL(IF(List="",last,IF(MATCH(List,List,0)=ROW(List)-mmm,ROW(List)-mmm,last-mmm)),ROW()-nnn))

List is the raw data with at least one blank row at the end. Remember to use absolute row references--e.g. $H$5:$H$802. This assumes that cell H802 is blank.
mmm is the row number of the row before List starts--in your case mmm would be 4
nnn is the row number of the row before the first formula to return list elements. If your formula is placed in cell U1, then mmm would be 0.
last is the row number of the last element (must be blank) in List--802 in your case
Note: formula returns blanks when the list is exhausted. It will also convert numbers to text.


=""&INDEX($H$5:$H$802,SMALL(IF($H$5:$H$802="",802,IF(MATCH($H$5:$H$802,$H$5:$H$802,0) _
=ROW($H$5:$H$802)-4,ROW($H$5:$H$802)-4,798)),ROW()))

This is an array formula, so remember to hold the Control and Shift keys down while pressing Enter. Excel should respond by adding curly braces { } surrounding the formula.

You would then create a dynamic named range for column U. This dynamic named range will be the Source for your data validation dropdown. Because column U contains empty strings from the above array formula, you must look for values that are at least one character long. The COUNTIF function can do this, using wildcards ?* as the match string. Here is the "Refers to" formula:


=$U$1:INDEX($U$1:$U$800,COUNTIF($U$1:$U$800,"=?*"))

Brad

lucas
04-16-2005, 01:56 PM
I like your solution. Much easier than setting up dynamic lists with formula's

just have to call the named range with =MyList in your data validation.

I also figured out how to have more than one list on your sheet of lists:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
n = Sheets("Lists").Range("A65536").End(xlUp).Row
ThisWorkbook.Names.Add Name:="MyList", RefersTo:=Sheets("Lists").Range("A1:A" & n)
ThisWorkbook.Names.Add Name:="MyList2", RefersTo:=Sheets("Lists").Range("B1:B" & n)
End Sub


Thanks Jake

Whoopsarenio,
that doesn't work when you update MyList2....must be a way...

Tried this extensivly, seems to work fine.
lets you have multiple lists on one list sheet.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
Dim r As Long
n = Sheets("Lists").Range("A65536").End(xlUp).Row
ThisWorkbook.Names.Add Name:="MyList", RefersTo:=Sheets("Lists").Range("A1:A" & n)
r = Sheets("Lists").Range("B65536").End(xlUp).Row
ThisWorkbook.Names.Add Name:="MyList2", RefersTo:=Sheets("Lists").Range("B1:B" & r)
End Sub

I can see where this is gonna be useful. If you see anything wrong with it, I would love to hear what might go wrong.

babycody
04-16-2005, 02:38 PM
byundt you understand exactly what I want as far as updating the list goes. I would really like to be able to do this in vba. I want the additions to the list to be permanent. Perhaps I should have mentioned that this is the master workbook. I want the complete list available everytime the workbook is opened. That means that H5:H801 will be completely blank everytime the workbook is opened, but I want the list to remain intact. So I would probably need vba to copy values>0 from column U and paste them into another column. Then the next time I open the workbook I would still have the list, and the code could use something like Range("A65536").End(xlUp).Offset(1).Select for that column before pasting again. H5:H801 would be connected to the list created by vba through data validation. I wish I could have a vba solution that would do what your formulas did, and make a permanent list that would be added onto more and more everytime the workbook was opened. I would probably have to use a clear contents for range H5:H801 and then save before closing to preserve the list after all the other code ran. That way I would have my blank master workbook and keep the list fully intact.

lucas perhaps you could explain to me what the code does besides creating two list. Maybe I am wrong, but doesn't it just name the ranges for A1:A and B1:B? How would this be useful to me. I feel that from your enthusiasm over Jakes code that I am missing something. If so please explain.

lucas
04-16-2005, 03:09 PM
BabyCody,
I have uploaded an example of Jakes code working. Download it and see if its what your looking for. You can add to the list by just typing in col A of the Lists worksheet. The Changes will be reflected in the Sheet1 Dropdowns that are highlighted.

I know the list is not in Col H:H as you first inquired, but this way your lists can be on a completly different sheet and could even be hidden from users. The list on the sheet "Lists" is dynamic (it grows as you add to it) because of the worksheet change code that Jake gave us.
Let me know if this helps.

babycody
04-16-2005, 03:46 PM
BabyCody,
I have uploaded an example of Jakes code working. Download it and see if its what your looking for. You can add to the list by just typing in col A of the Lists worksheet. The Changes will be reflected in the Sheet1 Dropdowns that are highlighted.

I know the list is not in Col H:H as you first inquired, but this way your lists can be on a completly different sheet and could even be hidden from users. The list on the sheet "Lists" is dynamic (it grows as you add to it) because of the worksheet change code that Jake gave us.
Let me know if this helps.

If I could combine this with what byundt gave me I would almost have what I want. Not losing hope though. Lucas try entering a value that isn't on the Lists sheet into sheet1. You will get an error message. What I would like to be able to do is enter anything into column A on sheet 1. If what I entered isn't on the Lists sheet then it will be added to the Lists sheet list automatically. So using the example.xls you made for me (thanks), if I enter 6 into sheet1!A1 then the list in the sheet labeled Lists would have the 6 added without me doing it manually. One problem that I have with using byundt example is that once H5:H801 is cleared then my list is cleared too. (not complaining byundt you are closest to understanding what I am trying to accomplish) Have you tried byundt's formulas Lucas? I think that using byundt's formulas ,or a coded version of it, combined with your code would get things closer to the solution. I have thought about copying the range that byundt's formulas are in and pasting special>values into another new column. Then putting in code that will search for the first blank cell in the new column and start adding new information at that point. I just don't know how to code it so that I wouldn't get redundant values. I guess I could use byundt's formulas on that also, and code again. Kind of like putting it through the wash twice. I do appreciate the time everyone has given on this so far. There are some really good ideas. Imagine a list that is updated just by entering new information into a cell. Kind of like double clicking on a google bar and seeing past search entries. Google remembered past searches you made and added them to the drop down list. Everytime you go to use Google it remembers what you entered from before.

lucas
04-16-2005, 04:24 PM
I have to admit I don't know how to do it yet, but I will work on it. Chances are someone will come along with an idea soon...
Sorry

babycody
04-16-2005, 08:15 PM
I have the answer from this post http://www.mrexcel.com/board2/viewtopic.php?p=682845#682845 Thanks Jindon. His code was a little incomplete at the end for some reason, but here is the complete code:


Private Sub Worksheet_Activate()
Dim LastR As Range
Set LastR = Range("j65536").End(xlUp)
Range("j5", LastR).Name = "SourceList"
With Range("g5:g100").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=SourceList"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = False
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastR As Range, x As Integer
With Target
If Intersect(Target, Range("g1:g501")) Is Nothing Then Exit Sub
If .Count = 1 And Not IsEmpty(.Value) Then
Set LastR = Range("j65536").End(xlUp)
x = Application.CountIf(Range("j5", LastR), .Value)
If x = 0 Then
LastR.Offset(1).Value = .Value
With Range("j5", LastR.Offset(1))
.Name = "SourceList"
.Sort key1:=Range("j5"), order1:=xlAscending
End With
Set LastR = Nothing
End If
End If
End With
End Sub

Directions:

Start at J5 and enter some names in several cells
Select range you wish to use for the list i.e. J5:J200
Choose from menu: insert>name>define
Names in workbook: SourceList (type SourceList into this area)
click on add
click on OK
Right click on Sheet1 tab
Select View Code
Copy the code from above and paste it into the window on the right
Choose from menu: Debug>Compile VBA Project
Click on the uppermost x to close the VBA screen
Click on Sheet2 tab (per Jindon's instructions)
Click on Sheet1 tab
Starting at G5 you should have a pulldown menus

NOW TRY ENTERING SOMETHING INTO G5 THAT ISN'T ON YOUR LIST

Now I just have to figure out how to save the list without saving the data entered into the cells of the other column. Remember you need to name the range the list is in SourceList. Of course you can name it whatever you like as long as you use that name in the code in the place where "SourceList" is.I think this will be a very handy piece of code when finished. Anyone want to help me put the finishing touches on it?:rofl:So happy:rotlaugh:

lucas
04-16-2005, 08:59 PM
Just curious as to why you want to clear the list each time.

might try workbook open...


Columns("J:J").Select
Selection.ClearContents

babycody
04-16-2005, 09:21 PM
Just curious as to why you want to clear the list each time.

might try workbook open...


Columns("J:J").Select
Selection.ClearContents

Because it is my master workbook. I don't want to clear the list. I want to clear the contents of the cells that have the pull down menus. Then I want to save changes. Otherwise I would have data entered into column G everytime I open the workbook. Here is the actual workbook if you would like to see it. Column G is the same thing in mine, but I will probably populate the list in Column U.

babycody
04-16-2005, 09:23 PM
Just curious as to why you want to clear the list each time.

might try workbook open...

Columns("J:J").Select
Selection.ClearContents

You need this to go along with the other attachment.

lucas
04-16-2005, 09:43 PM
I can't find a validation list in this workbook. No dropdowns. Unless I am missing something obvious(which is likely). And the code is locked with a password.

I don't know the path where the file is looking for the txt file...

babycody
04-16-2005, 09:51 PM
I can't find a validation list in this workbook. No dropdowns. Unless I am missing something obvious(which is likely). And the code is locked with a password.

I don't know the path where the file is looking for the txt file...

Haven't created the validation list in the master workbook yet. Attachment was a visual so you could get a sense of where I was going by clearing content of G5:G801. I also plan on clearing the contents and color filling A5:H801 with white apon closing workbook. The text file TEST gives you what you need to access the VBA code. I plan on posting the new workbook though once I get all the code in place. Did you like the code Jindon developed?

lucas
04-16-2005, 09:54 PM
yeah, I see where your going now a little better. When you paste in the info, the fields update. Will be looking forward to seeing this when you get it the way you want it.


Did you like the code Jindon developed?
I did find it interesting and kept it to use...thanks

babycody
04-17-2005, 06:58 AM
I am having trouble combining this piece of code with Jindon's code. Any suggestions?


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim LR As Long
Application.ScreenUpdating = False
' this will prevent the formatting from bleeding outside of the range
If Application.WorksheetFunction.CountA(Range(Cells(5, 1), Cells(65536, 1))) = 0 Then Exit Sub
Set rng = Columns(1)
If Intersect(Target, rng) Is Nothing Then Exit Sub
LR = Range("A65536").End(xlUp).Row 'finds the last row used in the range and stops formatting there
Set rng = Range("A5:H" & LR) 'LR stands for Last Range
With rng
rng.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""""" 'finds all the blank cells in the range and formats them
Selection.FormatConditions(1).Interior.ColorIndex = 35 'you choose the fill color here
Range("A65536").End(xlUp).Offset(1).Select 'returns you to the cell after the last cell with data in column A
End With
Application.ScreenUpdating = True
End Sub

babycody
04-17-2005, 09:01 AM
yeah, I see where your going now a little better. When you paste in the info, the fields update. Will be looking forward to seeing this when you get it the way you want it.


I did find it interesting and kept it to use...thanks

OK everything is working beautifuly now. I am attaching the workbook so you can test drive it.

lucas
04-17-2005, 09:55 AM
I had to change the file/save path, but other than that it works fine. One question, do you have to paste the data in..what I mean is, does it change frequently. If not, there must be a way to populate the cells without pasting from the text file in the example. Overall, looks nice, you can add to the list, refreshes itself, highlights cells that need attention, saves and emails the file. I haven't explored all of the formula's yet..:clap:

babycody
04-17-2005, 11:45 AM
I had to change the file/save path, but other than that it works fine. One question, do you have to paste the data in..what I mean is, does it change frequently. If not, there must be a way to populate the cells without pasting from the text file in the example. Overall, looks nice, you can add to the list, refreshes itself, highlights cells that need attention, saves and emails the file. I haven't explored all of the formula's yet..:clap:

Yeah I do have to paste from another program that could change at anytime. I haven't found any basic to expert articles on importing data from another program. I don't even know how to tell if the other program supports OLE or COM. Sometimes when people get ahead of others on a subject they forget how to explain things in simple terms with steps. The formulas are mainly for parsing the text. The one in column H looks up the content in Column B then assigns a numerical value to it based on the table in Q:R. That number is then added to a date in Column F. I have a code that makes the sheet expire on a certain date without a password. Another is the Excel Diet code found here. The button is used to save a copy(values only so no need for enable macros) of the worksheet(range B1:I:801) as the name produced in cell T1. It then emails the copy to a list of people. Then when I close the workbook I have code that wipes out the data that's been entered and removes conditional formating. Then it saves before closing. Now I just have to figure out what I am going to add to it next.:think::giggle