Type name into a cell removing name from another unique list Closed

Question

Is there a way to manually type a name into a cell on one worksheet, that removes that name/entry from another large list, with unique names, whether that list is on an additional worksheet (preferably) or in another location on the same worksheet?

Bonus question:  Can this be done using the large list mentioned on one worksheet, and simultaneously on smaller lists on other worksheets (or in other locations)?

I'm looking for either a simple formula method, or a more complex macro.

Thanks!

venkat1926 · Answer

your inquiry is rather vague to me. 

suppose in sheet 1 the data in column A is like this 

hdng1
a
s
d
f
g
h
j
k
l


now if you type a name in A1 of sheet if that name is available in sheet 1 that cell will be cleared. if not a msgbox will pop up.  for this do the following

right click TAB of sheet 2 reepat sheet 2 and click view code in the window that comes up copy this EVENT CODE

Private Sub Worksheet_Change(ByVal Target As Range)Dim x, cfind As RangeIf Target.Address <> "$A$1" Then GoTo enableeventsOn Error GoTo enableeventsApplication.enableevents = Falsex = TargetWith Worksheets("sheet1")Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)If Not cfind Is Nothing Thencfind.Cells.ClearElseMsgBox "that name is not availale in sheet1"End IfEnd Withenableevents:Application.enableevents = TrueEnd Sub

and save the file as macro enabled excel file

now enter any name in A1 (for e.g.. s or t) of sheet2 and see msgbox or sheet1

TO BE ON SAFE SIDE COPY SHEET1 IN SHEET 3 TO PRESERVE ORIGINAL DATA.

BR · Answer

Venkat26...thank you for your quick feedback. This may help as I'm trying to implement now.

Let me try this to avoid the vagueness. I have a list of 10,000 unique people with corresponding statistics on a worksheet. These are also included on additional worksheets into smaller subcategories, so they are listed twice: once on the large list, once on one of the smaller lists. I want to be able to type that name on a third sheet (whether it's the first few letters, or entire cell exactly) in each cell within a block or designated area of cells ("named"?, i.e., A2:S100) that once manually entered into a cell within that block on the third sheet (realizing it has to be exact), that entry/name is removed from the other larger and small lists from aforementioned Sheets 1 & 2. (If can only be done with one other worksheet, i.e. larger list, that is fine).

Example: I manually type "Miller, John" in B3 of Worksheet 3, and upon "hitting enter", macro or formulas remove "Miller, John" from Worksheet 1 or 2, or hopefully both.

Appreciate all your help!

venkat1926 · Answer

does not my macro do this? in the macro change the names of the sheet. if macro result is not what you want explain  what was wrong.

axxeshredder · Answer

It worked for Cell $A$1 on the second worksheet, but not for any other cells on that worksheet, and I ultimately need to fill in every cell from A1:S100 in the 2nd worksheet.  So if I have 1900 names, all 1900 cells in A1:S100 of the 2nd worksheet need to read off of cells A2 (A1 if no header) to A1901 in Worksheet 1.

Also, once I do the action, and type something on Worksheet not in the "list", the dialog box comes up perfectly.  Once I hit OK, though, and correct to a name on the list, the macro does not work.  For example, in your example, I typed "b" and the dialog box came up.  But then once I typed "a", which was in your list, nothing happened.

venkat1926 · Answer

try this 
if target.column<>1 then goto enableevents

axxeshredder · Answer

venkat1926....almost there...you're the best

now, in Worksheet 2, I just want to be able to do that same function in multiple columns (not just column A), preferably all the way to column S

so essentially, i want to be able to do it in a1, a2, a3, etc., b1, b2, b3, etc., c1, c2, c3, etc ...all the way over to column s.  And all the way down to row, let's say 50 for each of those columns.

venkat1926 · Answer

quote
now, in Worksheet 2, I just want to be able to do that same function in multiple columns (not just column A), preferably all the way to column S 

unquote


then change like this (first line in the  event code) as

if target.column >range("S1").column then goto enableevents

experiment  no.of times with target being any columns between A and S

feedback whether with this change the event code works.

axxeshredder · Answer

this works, however it only picks up entries as "Last Name" (my Column B in the list), and not as "Last Name, First Name" (my Column A), even if I type them exactly.  In both of these instances, the dialog box comes up.  And then after attempting "First Name" (my Column C) as an experiment, it's not picking up that name.  In this case, the dialog box does not come up.  

Example:

Column A          Column B       Column C
Miller, John        Miller              John

--  Does not read Column A, get dialog box (typed exactly the same)
--  Read Column B, name disappears (typed exactly)
--  Does not read Column C, does nothing (typed exactly)

Reason being is some individuals have the same last name, but not the same first name....but all names are unique

a) Why is this macro only capable of reading a single name, and not a "last name, first name"?
b)  And why does it only read the first column (Column B) with a single word, and not the next column (Column C)?  And why is it skipping over the first column (Column A)?

venkat1926 · Answer

suppose you type john,miller in sheet 1 in any cell rightclick TAb of sheet 2 and click view code and copy paste this event code Private Sub Worksheet_Change(ByVal Target As Range)Dim nname As String, cfind As RangeIf Target.Column <> 2 Then GoTo enableeventsApplication.enableevents = Falsenname = Target.Offset(0, -1) & "," & TargetSet cfind = Worksheets("sheet1").Cells.Find(what:=nname, lookat:=xlWhole)If Not cfind Is Nothing Then cfind.Cells.Clearenableevents:Application.enableevents = TrueEnd Sub now go to sheet 2 in column A type John and in B (same row) type miller see what happens remember when you type john,miller in sheet1 do not use any unnecessary spaces johnmiller if this entry is different modify the code nname=

Type name into a cell removing name from another unique list

9 responses

Similar discussions

Subscribe To Our Newsletter!