Type name into a cell removing name from another unique list

[Closed]
Report
Posts
8
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 15, 2014
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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!

9 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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 Range
If Target.Address <> "$A$1" Then GoTo enableevents
On Error GoTo enableevents
Application.enableevents = False
x = Target
With Worksheets("sheet1")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.Cells.Clear
Else
MsgBox "that name is not availale in sheet1"
End If
End With
enableevents:
Application.enableevents = True
End 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.
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!
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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.
Posts
8
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 15, 2014

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.
Posts
8
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 15, 2014

I tried the following two lines (separately) in Line 3 using your macro example:

If Target.Address <> "$A$1:$S$100" Then GoTo enableevents

then,

If Target.Address <> "draft" Then GoTo enableevents
(using "draft" as the named area for A1:S100 in Sheet2

Keeping as basic as possible, how/why does changing the worksheet names affect the outcome? Just curious, as I would keep those the same just to keep the model simple.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
try this
if target.column<>1 then goto enableevents
Posts
8
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 15, 2014

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.
Posts
8
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 15, 2014

let's just say EVERY cell in the block, or named group, in the area of A1:S50
If I can get to that point, that will suffice for the time being. But if not extremely time consuming, particularly from your end, I would like the name to not only come off that main Sheet (as it's doing now) but also off any and all of the remaining 7 sheets on the same workbook ("sub sheets" from the main sheet, or Worksheet 1 in our example), which in most instances, is only one other worksheet, or as I called them, "sub sheet".
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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.
Posts
8
Registration date
Thursday March 13, 2014
Status
Member
Last seen
March 15, 2014

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)?
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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 Range
If Target.Column <> 2 Then GoTo enableevents
Application.enableevents = False
nname = Target.Offset(0, -1) & "," & Target
Set cfind = Worksheets("sheet1").Cells.Find(what:=nname, lookat:=xlWhole)
If Not cfind Is Nothing Then cfind.Cells.Clear
enableevents:
Application.enableevents = True
End 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
john<comma>miller
if this entry is different modify the code
nname=